Join works in query but not in view-script

user_17614418 0
Viewed 17

I have a query with a join that works fine executing it directly (so without the "create view" part). For certain rows I get an averageDuration from the subquery T3. But when I build a view with this exact same query and Select all rows from this view, the join is not working. I get NULL as averageDuration where there was a value, executing the query directly. How is this possible?

The code uses views. Every tablename starting with "V_" is actually a view. To get this script working as a view, I need to change the join with V_Operationroom in subquery T3, to a normal table instead of the view. Strange thing about this solution is that the fields (Operationroom_Skey and IndOperationroom) used from the table or view (V_)Operationroom have the same datatype in table and view. The view actually gets them from the table (SELECT Operationroom_Skey, IndOperationroom FROM Operationroom).

CREATE VIEW [dbo].[V_WaitingList]
AS
    
SELECT  T1.Operationnumber, T3.AverageDuration

FROM V_Operation T1                                                                     
    
    LEFT JOIN V_Operationcode T2    ON  T1.Operationcode_Skey = T2.Operationcode_Skey                                                                       

    LEFT JOIN   (
                    SELECT  
                        S5.Operationcode,
                        AVG(S1.duration) AS AverageDuration
                    FROM V_F_Operatie S1
                        LEFT JOIN V_Operation S2 ON S1.Operation_SKey = S2.Operation_SKey
                        LEFT JOIN V_Operationroom S3 ON S1.Operationroom_Skey = S3.Operationroom_Skey
                        LEFT JOIN V_Operationcode S5 ON S2.Operationcode_Skey = S5.Operationcode_Skey   
                    WHERE   
                        S5.Operationcode IS NOT NULL AND
                        S3.IndOperationroom = '1'
                    GROUP BY    
                        S5.Operatieverrichtingcode

    )   T3  ON  T3.Operationcode = T2.Operationcode

GO
join view sql
1 Answers

Try using a CTE instead of a normal subquery like below;

CREATE VIEW [dbo].[V_WaitingList] AS
WITH CTEView AS (
     SELECT S5.Operationcode,
            AVG(S1.duration) AS AverageDuration
            FROM V_F_Operatie S1
            LEFT JOIN V_Operation S2 ON S1.Operation_SKey = S2.Operation_SKey
            LEFT JOIN V_Operationroom S3 ON S1.Operationroom_Skey = S3.Operationroom_Skey
            LEFT JOIN V_Operationcode S5 ON S2.Operationcode_Skey = S5.Operationcode_Skey   
            WHERE S5.Operationcode IS NOT NULL AND S3.IndOperationroom = '1'
            GROUP BY S5.Operatieverrichtingcode
)
SELECT  T1.Operationnumber, T3.AverageDuration
FROM V_Operation T1                                                                     
LEFT JOIN V_Operationcode T2 ON T1.Operationcode_Skey = T2.Operationcode_Skey                                                                       
LEFT JOIN CTEView T3 ON T3.Operationcode = T2.Operationcode
user_56453220
Related