Inner select statement not working in view-CodePudding

I have a view in TSQL that I am querying with a simple Select TOP 1 from vw_Dates where name = 'test'.

When I hardcode the name as test, in both parts (parts that are commented out), it works, the originalStartDate is populated. When I uncomment those parts, the logic works. How can I get this view to work where the outer select, and the inner select part know to use 'test' when I query this view with "Select TOP 1 from vw_Dates where name = 'test'"


CREATE VIEW [dbo].[vw_Dates] AS

SELECT *,
 (CASE
        WHEN originalStartDate < CurrentStartDate THEN originalStartDate
        
        ELSE CurrentStartDate
        END) AS CorrectStartDate 

FROM(
SELECT
 
    CurrentStartDate,   
   (select Top 1 startDate AS oldestStartDate from Dates dt
        where 
        --name = 'test' 
        --and 
        status = 'ACTIVE' order by startDate) AS originalStartDate,
  
 
   
FROM Dates
WHERE Dates.status = 'ACTIVE'
--and
--name = 'test' 

)x

CodePudding user response：

You need to correlate your sub-query using the Name column, and then rather than filtering in the view, filter when you select from the view.

CREATE VIEW [dbo].[vw_Dates]
AS
SELECT *
    , CASE
      WHEN originalStartDate < CurrentStartDate THEN originalStartDate        
      ELSE CurrentStartDate
      END AS CorrectStartDate 
FROM (
    SELECT dt.CurrentStartDate,  (
            SELECT TOP 1 dt1.startDate AS oldestStartDate
            FROM dbo.Dates dt1
            WHERE dt1.[Name] = dt.[Name]
            AND dt1.[Status] = dt.[Status]
            ORDER BY dt1.startDate
        ) AS originalStartDate
        , dt.[Name]
    FROM dbo.Dates dt
    WHERE dt.[Status] = 'ACTIVE'
) x;

SELECT *
FROM dbo.vw_Dates
WHERE [Name] = 'Test';

Note: I have correlated Status as well to avoid typing the same status twice.

And as HoneyBadger points out using MIN() is clearer e.g.

SELECT MIN(dt1.startDate) AS oldestStartDate
FROM dbo.Dates dt1
WHERE dt1.[Name] = dt.[Name]
AND dt1.[Status] = dt.[Status]

CodePudding user response：

you need to have a name column in the table x to achieve that

The sub select would profit, if xou have an index for type,status and startDate ASC, so you should try it as well as
HoneyBadger mentioning to use MIN(startDate) instead of ORDER BY and TOP i personally don't believe that it will get faster

CREATE VIEW [dbo].[vw_Dates] AS
SELECT *,
 (CASE
        WHEN originalStartDate < CurrentStartDate THEN originalStartDate
        
        ELSE CurrentStartDate
        END) AS CorrectStartDate 

FROM (
    SELECT
        name,
        CurrentStartDate,   
       (select Top 1 startDate AS oldestStartDate from Dates dt
            where 
            name = dt1.name 
            and 
            status = 'ACTIVE' order by startDate) AS originalStartDate
  
 
   
    FROM Dates dt1
    WHERE dt1.status = 'ACTIVE'
    )x