Use Derived/Temporary column in Where Clause

Question

Here Is my query. The Issue is that I am trying to apply the derived 'LineNo' field as the where clause. The query below does not work. Simply put, if the Value of a LineHrs column is > 0 it will set this derived column to a given value (eg If Line5Hrs = 1.4 then 'LineNo' for the row = 'Line 5'). I want to use this value to search for all jobs on a specific line.

SELECT      tblA.PROJECT_ID, 
            tblB.Line1Hrs, 
            tblB.Line2Hrs, 
            tblB.Line3Hrs, 
            tblB.Line4Hrs,
            tblB.Line5Hrs,
            tblB.Line6Hrs, 
            tblB.Line7Hrs, 
            "LineNo" =
                        CASE 
                            WHen tblB.Line1Hrs > 0 Then 'Line1'
                            WHen tblB.Line2Hrs > 0 Then 'Line2'
                            WHen tblB.Line3Hrs > 0 Then 'Line3'
                            WHen tblB.Line4Hrs > 0 Then 'Line4'
                            WHen tblB.Line5Hrs > 0 Then 'Line5'
                            WHen tblB.Line6Hrs > 0 Then 'Line6'
                            WHen tblB.Line7Hrs > 0 Then 'Line7'
                        End
FROM            tblA INNER JOIN tblB
                ON tblA.blah = tblB.blah AND
                tblA.blab = tblB.blab

WHERE         LineNo = 'Line5'

score 1 · Answer 1 · answered Feb 19 '19 at 12:35

you cannot use derived/alias column names inside where clause. Try to encapsulate query as subquery then put where on the main query.

select * from (
    SELECT      tblA.PROJECT_ID, 
                tblB.Line1Hrs, 
                tblB.Line2Hrs, 
                tblB.Line3Hrs, 
                tblB.Line4Hrs,
                tblB.Line5Hrs,
                tblB.Line6Hrs, 
                tblB.Line7Hrs, 
                "LineNo" =
                            CASE 
                                WHen tblB.Line1Hrs > 0 Then 'Line1'
                                WHen tblB.Line2Hrs > 0 Then 'Line2'
                                WHen tblB.Line3Hrs > 0 Then 'Line3'
                                WHen tblB.Line4Hrs > 0 Then 'Line4'
                                WHen tblB.Line5Hrs > 0 Then 'Line5'
                                WHen tblB.Line6Hrs > 0 Then 'Line6'
                                WHen tblB.Line7Hrs > 0 Then 'Line7'
                            End
    FROM            tblA 
    INNER JOIN tblB
        ON tblA.blah = tblB.blah AND
           tblA.blab = tblB.blab
)
WHERE   LineNo = 'Line5'

score 0 · Answer 2 · answered Feb 19 '19 at 12:34

Use a CTE or subquery. For example:

WITH CTE AS
    (SELECT tblA.PROJECT_ID,
            tblB.Line1Hrs,
            tblB.Line2Hrs,
            tblB.Line3Hrs,
            tblB.Line4Hrs,
            tblB.Line5Hrs,
            tblB.Line6Hrs,
            tblB.Line7Hrs,
            CASE
                 WHEN tblB.Line1Hrs > 0 THEN 'Line1'
                 WHEN tblB.Line2Hrs > 0 THEN 'Line2'
                 WHEN tblB.Line3Hrs > 0 THEN 'Line3'
                 WHEN tblB.Line4Hrs > 0 THEN 'Line4'
                 WHEN tblB.Line5Hrs > 0 THEN 'Line5'
                 WHEN tblB.Line6Hrs > 0 THEN 'Line6'
                 WHEN tblB.Line7Hrs > 0 THEN 'Line7'
            END AS [LineNo]
     FROM tblA
          INNER JOIN tblB ON tblA.blah = tblB.blah
                         AND tblA.blab = tblB.blab)
SELECT PROJECT_ID,
       Line1Hrs,
       Line2Hrs,
       Line3Hrs,
       Line4Hrs,
       Line5Hrs,
       Line6Hrs,
       Line7Hrs,
       [LineNo]
FROM CTE
WHERE [LineNo] = 'Line5';

You cannot reference a column (by alias) in your SELECT in the WHERE clause, because the WHERE is evaluated before the SELECT: Logical Processing Order of the SELECT statement

score 0 · Answer 3 · answered Feb 19 '19 at 12:35

I like to use apply for this purpose:

SELECT . . ., v.lineno
FROM tblA a INNER JOIN
     tblB b
     ON a.blah = b.blah AND
        a.blab = b.blab CROSS APPLY
     (VALUES (CASE WHEN b.Line1Hrs > 0 Then 'Line1'
                   WHEN b.Line2Hrs > 0 Then 'Line2'
                   WHEN b.Line3Hrs > 0 Then 'Line3'
                   WHEN b.Line4Hrs > 0 Then 'Line4'
                   WHEN b.Line5Hrs > 0 Then 'Line5'
                   WHEN b.Line6Hrs > 0 Then 'Line6'
                   WHEN b.Line7Hrs > 0 Then 'Line7'
              END)
     ) v(LineNo)
WHERE v.LineNo = 'Line5'

score 0 · Answer 4 · answered Feb 19 '19 at 12:35

I would use APPLY as you can't use derived/alias column names with WHERE clause :

SELECT . . . 
FROM tblA INNER JOIN 
     tblB
     ON tblA.blah = tblB.blah AND 
        tblA.blab = tblB.blab  CROSS APPLY
     ( VALUES (CASE WHen tblB.Line1Hrs > 0 Then 'Line1'
                    WHen tblB.Line2Hrs > 0 Then 'Line2'
                    WHen tblB.Line3Hrs > 0 Then 'Line3'
                    WHen tblB.Line4Hrs > 0 Then 'Line4'
                    WHen tblB.Line5Hrs > 0 Then 'Line5'
                    WHen tblB.Line6Hrs > 0 Then 'Line6'
                    WHen tblB.Line7Hrs > 0 Then 'Line7'
               End)  
    ) BB (LineNo )
Where BB.LineNo = 'Line5';

Use Derived/Temporary column in Where Clause

4 Answers4