Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Tue, 12 Dec 2006 @ 17:40:15 GMT


     
  <Prev Next>  
<<First
<Prev
Next> Last>>  


Subj:   Assigning a default value to derived values in
 
From:   Jack.Richter

When I do the a cube of data, I can't see how to assign a default value to the 'null' derived column values. I tried the following but the coalesce is ignored on the derived values.

     select
             coalesce(extract(year from post_date),3000)  "Year",
             coalesce(extract(month from post_date), 3000) "Month",
             sum(case when detail_type = 41 then 1 else 0 end) as "TTL_TRANS_CNT",
             sum(case when detail_type = 60 then amount else 0 end) as "TTL_TRANS_AMT"
     from CLARITY_TDL_AGE
     where extract(year from post_date) = 2004
     and   extract(month from post_date) in (10,11)
     group by cube (1,2)
     order by coalesce("Year",3000) ASC, coalesce("Month",13) ASC;

        Year       Month     TTL_TRANS_CNT     TTL_TRANS_AMT
        2004       10                    0        3280806.97
        2004       11                    0        4031742.85
        2004                       0        7312549.82
             10                    0        3280806.97
             11                    0        4031742.85
                             0        7312549.82

Regards,

Jack A. Richter
Data Mgt. Consultant/Specialist
Kaiser Permanente



     
  <Prev Next>  
<<First
<Prev
Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016