1

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

Nikolay Kostov
  • 16,433
  • 23
  • 85
  • 123
Srinivas P
  • 31
  • 3
  • 9
  • 2
    What do you mean by *handle NULLs*? Replace it with a default? e.g. `COALESCE(max(ss_sb1.snapshot_time), CURRENT_DATE)` – dnoeth Jul 27 '15 at 16:53

2 Answers2

2

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

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

Sudhanshu Agrawal
  • 412
  • 3
  • 9
  • Will this implicitly convert the DATE column to a CHAR? My experience with COALESCE is that the value to be replaced for NULL has to be the same data type as the column being tested. – Rob Paller Jul 28 '15 at 18:57
  • Yes you are correct here, what we can do is to convert the date to char using the CAST function : COALESCE((CAST(datecolumn as varchar2(10))),'')<>'' – Sudhanshu Agrawal Jul 28 '15 at 19:09
0

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;
access_granted
  • 1,807
  • 20
  • 25