CODEWITHSUNDEEP
teradata
Srinivas P
Srinivas P

Reputation: 31

how to handle nulls in date coulmn in teradata

This is my sql and i want to handle nulls for date coulmn .

sql:

insert into r_intraday_netrev
      ( report_time, snapshot_time, prodtype, qty, yestasp,
        m2_cumpct, m2_predictedunits, m2_rev, m3_cumpct, m3_predictedunits, m3_rev)
select current_time as report_time,
        **max(ss_sb1.snapshot_time),**------want to handle nulls for this date coulmn 
        'ZTOTAL',
        null,
        null,
        null,
        null,
        sum(round( ( (ss_sb1.qty / ss_p2.cumpct) * ss_sb2.asp ) , 0)),
        null,
        null,
        sum(round( ( (ss_sb1.qty / ss_p3.cumpct) * ss_sb2.asp) , 0)) 
from DW_CORE.ss_topline_a_v  ss_sb1
left outer join DW_CORE.ss_topline_rsst_b_v ss_sb2 ON ss_sb1.prodtype = ss_sb2.prodtype 
left outer join DW_CORE.ss_predict_p2_v  ss_p2 ON  (ss_sb1.prodtype = ss_p2.prodtype AND td_day_of_week(ss_sb1.snapshot_time) = ss_p2.dayofweek 
AND substr(to_char(ss_sb1.snapshot_time, 'HH24MI' ),1,3)||'0' =  ss_p2.timeofday)
left outer join DW_CORE.ss_predict_p3_v  ss_p3 ON  (ss_sb1.prodtype = ss_p3.prodtype AND (snapshot_time - interval '364' day) = ss_p3.lastyear_date 
AND substr(to_char(snapshot_time, 'HH24MI' ),1,3)||'0' =  ss_p3.lastyear_time) ;

can you please anyone help me ...

Upvotes: 1

Views: 9890

Answers (2)

access_granted
access_granted

Reputation: 1927

Assuming you want to default to, let's say, the YEND of 2019:

select coalesce(date_column, to_date('2019-12-31')) from your_table;

Upvotes: 0

Sudhanshu Agrawal
Sudhanshu Agrawal

Reputation: 432

You can use the below to check for NULLS in date:

COALESCE((CAST(datecolumn as varchar2(10))),'')<>'' '

Upvotes: 2

Related Questions