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.
Best Answer
The problem arises because the aliases defined in the
SELECT
list are not visible by the same levelWHERE
clause - and the reason behind this is the order of logical execution of queries (which is roughlyFROM -> 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 theWHERE
clause (or in theSELECT
and in theHAVING
clause, if there is aGROUP 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 variousWHERE
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 andOUTER 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. TheAPPLY
would look like:Then we add the
APPLY
correlated subqueries toFROM
clause:Now, we can use the subqueries results in the
WHERE
clause: