How do I reference a calculated field?

[Mack2]

Within a sql query, I am creating a field based on a case statement.
CASE WHEN X IS NULL THEN 0 ELSE X END Temp

Now I need to compare the created field (Temp) to an existing field. I have tried a sub query, but no luck. Thank you in advance for your help.

 

[RiverGuy]

Can you post your query you have so far? The answer depends on which part of the query you are referencing it.

 

[markros]

select * from (select *, myCaseField as myExtraField from myTable) Extra where myRegularField = myExtraField.

 

[bborissov]

You could use tha same CASE statement in the WHERE clause:

SELECT *, CASE .... END AS Temp
FROM YourTable
WHERE Field = CASE .... END -- the same CASE as the one above

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[r937]

and you could also change this --

CASE WHEN X IS NULL THEN 0 ELSE X END Temp

to this --

COALESCE(X,0) Temp



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Mack2]

This is my case statement

CASE WHEN CURR_AWD_RANK_NO IS NULL THEN 0 ELSE CURR_AWD_RANK_NO END CURR_AWD_RANK_NO_TEST

Now, I need to compare CURR_AWD_RANK_NO_TEST to another field in the query. An example is below.

where CURR_AWD_RANK_NO = CURR_AWD_RANK_NO_TEST

Hope this helps.

 

[Mack2]

I do not think those items will work. If I use COALESCE(X,0) Temp , then how do I compare another field to Temp?

Thanks!

 

[r937]

in that example, you're comparing the value of the COALESCE'd column back to itself???

the general form of what you want has already been given by markros

just use the column alias in an outer query --

SELECT [i]other columns[/i]
     , [blue]CURR_AWD_RANK_NO_TEST[/blue]
  FROM (
       SELECT [i]other columns[/i]
            , COALESCE(CURR_AWD_RANK_NO,0) AS [blue]CURR_AWD_RANK_NO_TEST[/blue]
         FROM daTable
       ) AS d
 WHERE [blue]CURR_AWD_RANK_NO_TEST[/blue] ...

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Mack2]

Below is the full query. I need to convert DV.PIN_AWD_RNK_NO to 0 if it is null.....thanks!!!!

select DV.SNAP_MO_YR_KEY_NO
,case
when DV.PIN_AWD_RNK_NO = GLOSS.PIN_AWD_RANK_NO then 'YES'
else 'NO' END as Sponsor_Match_Flag
,count(1) as IMC_CNT
from "DWSANL01"."DV_IMC_LOS_CCYYMM_SEQ_DIM" DV
inner join DWSGDW01.DWT00420_LOS_DIM_CCYYMM_FACT GLOSS on
DV.SNAP_MO_YR_KEY_NO = GLOSS.SNAP_MO_YR_KEY_NO
and DV.IMC_KEY_NO = GLOSS.IMC_KEY_NO
group by DV.SNAP_MO_YR_KEY_NO
,case
when DV.PIN_AWD_RNK_NO = GLOSS.PIN_AWD_RANK_NO then 'YES'
else 'NO' END
order by DV.SNAP_MO_YR_KEY_NO
,case
when DV.PIN_AWD_RNK_NO = GLOSS.PIN_AWD_RANK_NO then 'YES'
else 'NO' END ;

 

[r937]

SELECT snap_mo_yr_key_no
     , [blue]sponsor_match_flag[/blue]
     , COUNT(*) AS imc_cnt
  FROM (
       SELECT dv.snap_mo_yr_key_no
            , CASE WHEN dv.pin_awd_rnk_no = gloss.pin_awd_rank_no 
                   THEN 'yes'
                   else 'no' END AS [blue]sponsor_match_flag[/blue]
         FROM dwsanl01.dv_imc_los_ccyymm_seq_dim AS dv
       INNER 
         JOIN dwsgdw01.dwt00420_los_dim_ccyymm_fact AS gloss 
           ON gloss.snap_mo_yr_key_no = dv.snap_mo_yr_key_no
          AND gloss.imc_key_no = dv.imc_key_no
       ) AS d
GROUP 
    BY snap_mo_yr_key_no
     , [blue]sponsor_match_flag[/blue]
ORDER 
    BY dv.snap_mo_yr_key_no
     , [blue]sponsor_match_flag[/blue]

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon