|
|
Archives of the TeradataForum
Message Posted: Tue, 12 Dec 2006 @ 17:40:15 GMT
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
| |