Selecting an alias in case t-sql

Question

I have come across an issue which I am not sure how to fix. In my stored procedure I use DATEDIFF() function to return an int for date difference. In my logic I have also subtracted a value from the returned value of DATEDIFF() which is giving me a negative value. This is by design as this logic is for working out rates.

My problem is I am using a case statement but I can't select the column because it is using an Alias. What I aim to do is to write some additional logic where if the value is negative I want it to display as 0.

See my code below for more information:

DATEDIFF(d, vso.pod_ata, ISNULL(cncr.cntr_date3, GETUTCDATE())) - 14  AS dem_days, -- Date difference to work out Demurrage days
DATEDIFF(d, cncr.dc_ata, ISNULL(cncr.empty_return_dt, GETUTCDATE())) - 21  AS det_days,

CASE WHEN dem_days <= 0 THEN 0 END AS 'test1',
CASE WHEN det_days <= 0 THEN 0 END AS 'test2',

Error message is:

Invalid column name 'dem_days'. Invalid column name 'det_days'.

What is the best solution to fix this?

score 4 · Accepted Answer · answered Mar 16 '12 at 10:01

4

use this datediff function whenever you need.

DATEDIFF(d, vso.pod_ata, ISNULL(cncr.cntr_date3, GETUTCDATE())) - 14  AS dem_days, -- Date difference to work out Demurrage days
DATEDIFF(d, cncr.dc_ata, ISNULL(cncr.empty_return_dt, GETUTCDATE())) - 21  AS det_days,

CASE WHEN DATEDIFF(d, vso.pod_ata, ISNULL(cncr.cntr_date3, GETUTCDATE())) - 14 <= 0 THEN 0 END AS 'test1',
CASE WHEN DATEDIFF(d, cncr.dc_ata, ISNULL(cncr.empty_return_dt, GETUTCDATE())) - 21 <= 0 THEN 0 END AS 'test2',

answered Mar 16 '12 at 10:01

Hiren Visavadiya

485
1
3
15

That worked perfectly. I added an else condition where it won't evaluate the value if it is not less than zero. Which will show me either a zero or a number greater than zero but at the same time keeping my rates logic intact. Thank you sir!! – nick gowdy Mar 16 '12 at 10:21

score 1 · Answer 2 · edited May 23 '17 at 11:56

You cannot use column alias in your WHERE clause. You would need to repeat the statements from your SELECT:

SELECT
DATEDIFF(d, vso.pod_ata, ISNULL(cncr.cntr_date3, GETUTCDATE())) - 14  AS dem_days, -- Date difference to work out Demurrage days
DATEDIFF(d, cncr.dc_ata, ISNULL(cncr.empty_return_dt, GETUTCDATE())) - 21  AS det_days
FROM Table
WHERE
CASE WHEN DATEDIFF(d, vso.pod_ata, ISNULL(cncr.cntr_date3, GETUTCDATE())) - 14 <= 0 THEN 0 END AS 'test1',
CASE WHEN DATEDIFF(d, cncr.dc_ata, ISNULL(cncr.empty_return_dt, GETUTCDATE())) - 21 <= 0 THEN 0 END AS 'test2'

However, if you need to use a WHERE clause with alias names, you can structure your query like this:

select *
from 
   (
   select a + b as aliased_column
   from table
   ) dt
where dt.aliased_column = something.

See " Using an Alias column in the where clause in ms-sql 2000 "

That `HAVING` bit looks suspect - the expressions are returning values, whereas `HAVING` clauses only evaluate predicates. — Damien_The_Unbeliever, Mar 16 '12 at 10:16
@Damien_The_Unbeliever Good point, I've removed it from my answer — Curtis, Mar 16 '12 at 10:24

Damien_The_Unbeliever · Answer 3 · 2012-03-16T10:16:00.587

If you want to reuse calculations in your SELECT for other columns within the select, or in other parts of the query, you need to move the calculations into a subselect or CTE. E.g.:

SELECT
    --Useful columns for final result (e.g. other columns from your original query)
    CASE WHEN dem_days <= 0 THEN 0 END AS 'test1',
    CASE WHEN det_days <= 0 THEN 0 END AS 'test2'
FROM
(
    SELECT
         --Other columns (not shown in OPs question)
          DATEDIFF(d, vso.pod_ata, ISNULL(cncr.cntr_date3, GETUTCDATE())) - 14  AS dem_days, -- Date difference to work out Demurrage days
          DATEDIFF(d, cncr.dc_ata, ISNULL(cncr.empty_return_dt, GETUTCDATE())) - 21  AS det_days
    FROM
        --Original Query
) t

The t after the final end-bracket is just because the sub-selected table has to have an alias.

If you have calculations based on calculations based on calculations (etc), then the sub-select style can start to look a little ugly/confusing, so the CTE form might be preferred:

;With FirstCalcs as (
     SELECT BaseColumn1,BaseColumn2,CalculatedColumn1,CalculatedColumn2
     FROM ....
), SecondCalcs as (
     SELECT BaseColumn1,BaseColumn2,CalculatedColumn1,CalculatedColumn3 --3 depends on 1 & 2
     FROM FirstCalcs
), ThirdCalcs as (
     SELECT BaseColumn1,BaseColumn2,CalculatedColumn1
     FROM SecondCalcs
     WHERE CalculatedColumn3 = <Condition>
)
SELECT <Final result columns>
FROM ThirdCalcs

And at least that way, the nesting doesn't get out of hand.

score 0 · Answer 4 · answered Mar 16 '12 at 10:29

This is a job for CROSS APPLY.

SELECT D.dem_days
    , D.det_days
    , T.test1
    , T.test2
FROM cncr
CROSS APPLY (SELECT
    dem_days = DATEDIFF(d, cncr.vso.pod_ata, ISNULL(cncr.cntr_date3, 
               GETUTCDATE())) - 14
    , det_days = DATEDIFF(d, cncr.dc_ata, ISNULL(cncr.empty_return_dt,
                 GETUTCDATE())) - 21
) D
CROSS APPLY (SELECT
    test1 = CASE WHEN D.dem_days <= 0 THEN 0 END
    , test2 = CASE WHEN D.det_days <= 0 THEN 0 END
) T

Selecting an alias in case t-sql

4 Answers4