Referencing a SELECT subquery value in the WHERE clause

Question

Complete query

SET @vSelect = 
    '
    SELECT                       
        T1.BranchShortName AS BranchShortName,
        (
            SELECT TT1.EmployeeName
            FROM [dbo].[Tb_OPL_Employee] AS TT1
            JOIN [dbo].[Tb_MKT_SKD] AS TT2
                ON TT2.IdTb_OPL_Branch = TT1.IdTb_OPL_Branch
            JOIN [dbo].[Tb_SYS_Approval] AS TT3
                ON TT3.TaskId = TT2.TaskId
            WHERE
                TT3.IdTb_OPL_JobTitles = TT1.IdTb_OPL_JobTitles
                AND TT3.IdTb_OPL_JobTitles = 
                (
                    SELECT MAX(TTT3.IdTb_OPL_JobTitles) 
                    FROM [dbo].[Tb_SYS_Approval] AS TTT3 
                    WHERE 
                        TTT3.IsPassed = 1 
                        AND TTT3.ApprovalLevelCode = ''Approve'' 
                        AND TTT3.TaskId =TT2.TaskId
                )
                AND TT3.IdTb_OPL_Branch = TT1.IdTb_OPL_Branch
                AND TT2.IdTb_MKT_SKD = T2.IdTb_MKT_SKD
        ) AS LastApproval,
        (
            SELECT TT1.EmployeeName 
            FROM [dbo].[Tb_OPL_Employee] AS TT1
            JOIN [dbo].[Tb_MKT_SKD] AS TT2
                ON TT2.IdTb_OPL_Branch = TT1.IdTb_OPL_Branch
            JOIN [dbo].[Tb_SYS_Approval] AS TT3
                ON TT3.TaskId = TT2.TaskId
            WHERE 
                TT1.IdTb_OPL_JobTitles = TT3.IdTb_OPL_JobTitles
                AND TT3.IdTb_OPL_JobTitles = 
                (
                    SELECT MIN(TTT3.IdTb_OPL_JobTitles) 
                    FROM [dbo].[Tb_SYS_Approval] AS TTT3 
                    WHERE
                        TTT3.IsPassed = 0 
                        AND TTT3.ApprovalLevelCode = ''Approve'' 
                        AND TTT3.TaskId =TT2.TaskId
                )
                AND TT3.IdTb_OPL_Branch = TT1.IdTb_OPL_Branch
                AND TT2.IdTb_MKT_SKD = T2.IdTb_MKT_SKD
        ) AS NextApproval,
        T6.StatusDescription AS Status
    '
SET @vWhere = ' WHERE 1=1 '

IF (@ddlSearchCriteria = '1') 
BEGIN
    -- The condition of search textbox is not empty
    IF (@txtSearch <> '')
    BEGIN
        -- The Condition of StartDate is not empty and EndDate is empty
        IF (@txtStartDate <> '') AND (@txtEndDate = '')
            SET @vWhere = @vWhere + ' AND T1.BranchShortName LIKE ''%' + ltrim(rtrim(@txtSearch)) + '%'' 
                                      AND T2.CreatedDate >= CONVERT(SMALLDATETIME, ''' + @txtStartDate + ''', 101)'

        -- The Condition of StartDate is empty and EndDate is not empty
        IF (@txtStartDate = '') AND (@txtEndDate <> '')
            SET @vWhere = @vWhere + ' AND T1.BranchShortName LIKE ''%' + ltrim(rtrim(@txtSearch)) + '%'' 
                                      AND T2.CreatedDate <= CONVERT(SMALLDATETIME, ''' + @txtEndDate + ''', 101)'

        -- The Condition of StartDate is not empty and EndDate is not empty
        IF (@txtStartDate <> '') AND (@txtEndDate <> '')
            SET @vWhere = @vWhere + ' AND T1.BranchShortName LIKE ''%' + ltrim(rtrim(@txtSearch)) + '%'' 
                                      AND T2.CreatedDate >= CONVERT(SMALLDATETIME, ''' + @txtStartDate + ''', 101)
                                      AND T2.CreatedDate <= CONVERT(SMALLDATETIME, ''' + @txtEndDate + ''', 101)'

        IF (@txtStartDate = '') AND (@txtEndDate = '')
            SET @vWhere = @vWhere + ' AND T1.BranchShortName LIKE ''%' + ltrim(rtrim(@txtSearch)) + '%'''

        PRINT @vSelect+@vWhere
    END

How can I get the subquery value for LastApproval to compare like I do with T1.BranchShortName?

I tried changing it like:

... IF (@txtStartDate <> '') AND (@txtEndDate = '')
            SET @vWhere = @vWhere + ' AND **T1.BranchShortName --> LastApproval** LIKE ''%' + ltrim(rtrim(@txtSearch)) + '%''  ...

but this gave me a message saying there was an error in my where clause.

Answer 1

The problem arises because the aliases defined in the SELECT list are not visible by the same level WHERE clause - and the reason behind this is the order of logical execution of queries (which is roughly FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY, omitting some other clauses).

You can bypass this problem with several methods:

• duplicating the expression code for the alias (the subqueries code here), so the code is both in the SELECT and in the WHERE clause (or in the SELECT and in the HAVING clause, if there is a GROUP BY and the expression uses aggregated values). This might result in a query which looks more complicated that it needs to be - and it may result in errors/inconsistencies later (if a developer changes the subquery/expression only in 1 of the 2 places).

• move the expression code (subquery in our case) to a CTE. This may not be possible or easy to do in all cases - eg. if the expression is a correlated subquery, like in the specific issue.

• wrap the whole query into a derived table (or CTE) and move the conditions for those (calculated, aliased) columns to the external WHERE clause. This should work in all cases. It may not be the most efficient though in every case. If the optimizer can "push down" the condition, the plan should be identical to solution 1 (having the code duplicated) as the optimizer can then apply further transformations and choose between different execution paths (in regard with checking the various WHERE conditions and the order of joining tables). If the condition is/can not pushed down, it will have to be evaluated after the derived table which may not be the most efficient in some cases.

• use OUTER APPLY instead of duplicating code. This is possible only if the expression does not use aggregated values. (If it uses aggregation, it will need more complex rewriting, using a derived table and OUTER APPLY.)
  This in my opinion the best option, as the condition is already "pushed down" to the same level as the rest joins and conditions and we are sure the optimizer has more options to plan the execution. Plus the code is only in one place.

For your case, solutions 1 (duplicate code), 3 (wrap the whole query in a derived table) and 4 (the OUTER APPLY) seem to be doable. The APPLY would look like:

-- the SELECT, with the subqueries removed
SET @vSelect = '
        SELECT    
             T1.BranchShortName AS BranchShortName,
             LA.LastApproval,
             NA.NextApproval,
             T6.StatusDescription AS Status
'
-- the FROM as it was (whatever you have there, doesn't change)
SET @vFrom = '
        FROM dbo.table_1 AS T1
          JOIN dbo.table_2 AS T2
          ON  T1.x = T2.y
'

Then we add the APPLY correlated subqueries to FROM clause:

-- OUTER APPLY subqueries added
SET @vFrom = @vFrom + '
          OUTER APPLY
            ( SELECT TT1.EmployeeName AS LastApproval
              FROM [dbo].[Tb_OPL_Employee] AS TT1
                JOIN [dbo].[Tb_MKT_SKD] AS TT2
                ON TT2.IdTb_OPL_Branch = TT1.IdTb_OPL_Branch
                JOIN [dbo].[Tb_SYS_Approval] AS TT3
                ON TT3.TaskId = TT2.TaskId
              WHERE TT3.IdTb_OPL_JobTitles = TT1.IdTb_OPL_JobTitles
                AND TT3.IdTb_OPL_JobTitles = 
                    ( SELECT MAX(TTT3.IdTb_OPL_JobTitles) 
                      FROM [dbo].[Tb_SYS_Approval] AS TTT3 
                      WHERE TTT3.IsPassed = 1 
                        AND TTT3.ApprovalLevelCode = ''Approve'' 
                        AND TTT3.TaskId = TT2.TaskId)
                AND TT3.IdTb_OPL_Branch = TT1.IdTb_OPL_Branch
                AND TT2.IdTb_MKT_SKD = T2.IdTb_MKT_SKD
            ) AS LA (LastApproval)
          OUTER APPLY
            ( SELECT TT1.EmployeeName AS NextApproval
              FROM [dbo].[Tb_OPL_Employee] AS TT1
                JOIN [dbo].[Tb_MKT_SKD] AS TT2
                ON TT2.IdTb_OPL_Branch = TT1.IdTb_OPL_Branch
                JOIN [dbo].[Tb_SYS_Approval] AS TT3
                ON TT3.TaskId = TT2.TaskId
              WHERE TT1.IdTb_OPL_JobTitles = TT3.IdTb_OPL_JobTitles
                AND TT3.IdTb_OPL_JobTitles = 
                    ( SELECT MIN(TTT3.IdTb_OPL_JobTitles) 
                      FROM [dbo].[Tb_SYS_Approval] AS TTT3 
                      WHERE TTT3.IsPassed = 0 
                        AND TTT3.ApprovalLevelCode = ''Approve'' 
                        AND TTT3.TaskId = TT2.TaskId)
                AND TT3.IdTb_OPL_Branch = TT1.IdTb_OPL_Branch
                AND TT2.IdTb_MKT_SKD = T2.IdTb_MKT_SKD
            ) AS NA (NextApproval)
'

Now, we can use the subqueries results in the WHERE clause:

SET @vWhere = ' WHERE 1=1 '

IF (@ddlSearchCriteria = '1') 
BEGIN
    -- The condition of search textbox is not empty
    IF (@txtSearch <> '')
    BEGIN
        -- The Condition of StartDate is not empty and EndDate is empty
        IF (@txtStartDate <> '') AND (@txtEndDate = '')
            SET @vWhere = @vWhere + ' AND T1.BranchShortName LIKE ''%' + ltrim(rtrim(@txtSearch)) + '%'' 
                                      AND T2.CreatedDate >= CONVERT(SMALLDATETIME, ''' + @txtStartDate + ''', 101)'

        -- The Condition of StartDate is empty and EndDate is not empty
        IF (@txtStartDate = '') AND (@txtEndDate <> '')
            SET @vWhere = @vWhere + ' AND T1.BranchShortName LIKE ''%' + ltrim(rtrim(@txtSearch)) + '%'' 
                                      AND T2.CreatedDate <= CONVERT(SMALLDATETIME, ''' + @txtEndDate + ''', 101)'

        -- The Condition of StartDate is not empty and EndDate is not empty
        IF (@txtStartDate <> '') AND (@txtEndDate <> '')
            SET @vWhere = @vWhere + ' AND T1.BranchShortName LIKE ''%' + ltrim(rtrim(@txtSearch)) + '%'' 
                                      AND T2.CreatedDate >= CONVERT(SMALLDATETIME, ''' + @txtStartDate + ''', 101)
                                      AND T2.CreatedDate <= CONVERT(SMALLDATETIME, ''' + @txtEndDate + ''', 101)'

        -- we can use LA.LastApproval as any other column
        -- and the OUTER APPLY becomes CROSS APPLY
        IF (@txtStartDate = '') AND (@txtEndDate = '')
            SET @vWhere = @vWhere + ' AND LA.LastApproval LIKE ''%' + ltrim(rtrim(@txtSearch)) + '%'''

        PRINT @vSelect + @vFrom + @vWhere + ' ;'
    END

Answer 2

_{Community Wiki answer to document the answer originally edited into the question by the question author.}

---

I used a variable to hold the subquery value:

SET @A = 
(
    SELECT TT1.EmployeeName 
    FROM [dbo].[Tb_OPL_Employee] AS TT1
    JOIN [dbo].[Tb_MKT_SKD] AS TT2
        ON TT2.IdTb_OPL_Branch = TT1.IdTb_OPL_Branch
    JOIN [dbo].[Tb_SYS_Approval] AS TT3
        ON TT3.TaskId = TT2.TaskId
    WHERE 
        TT1.IdTb_OPL_JobTitles = TT3.IdTb_OPL_JobTitles
        AND TT3.IdTb_OPL_JobTitles = 
        (
            SELECT MIN(TTT3.IdTb_OPL_JobTitles) 
            FROM [dbo].[Tb_SYS_Approval] AS TTT3 
            WHERE TTT3.IsPassed = 0 
            AND TTT3.ApprovalLevelCode = ''Approve'' 
            AND TTT3.TaskId =TT2.TaskId
        )
        AND TT3.IdTb_OPL_Branch = TT1.IdTb_OPL_Branch
        AND TT2.IdTb_MKT_SKD = T2.IdTb_MKT_SKD
);