Archives of the TeradataForum

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

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

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>>