All Forums Database
nivas567 1 post Joined 01/16
16 Jan 2016
Invalid calculation: division by zero

Hi,
 
I'm trying to insert into a table using below sql getting 'division by zero ' error.
 
Could any of you help me how to fix it.
 
 
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, 
                ss_sb1.snapshot_time, 
                ss_sb1.prodtype, 
                ss_sb1.qty, 
                ss_sb2.asp                                             AS yestasp, 
                ss_p2.cumpct                                              m2_cumpct, 
                Round( ( (ss_sb1.qty / ss_p2.cumpct) ) , 0)               m2_predictedunits, 
                round( ( (ss_sb1.qty / ss_p2.cumpct) ss_sb2.asp ) , 0)    m2_rev, 
                ss_p3.cumpct                                              m3_cumpct, 
                round( ( (ss_sb1.qty / ss_p3.cumpct) ) , 0)               m3_predictedunits, 
                round( ( (ss_sb1.qty / ss_p3.cumpct) ss_sb2.asp) , 0)     m3_rev 
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)

dnoeth 4628 posts Joined 11/04
16 Jan 2016

There's a simple rule to avoid this error, whenever you divide use NULLIF, e.g.:

ss_sb1.qty / NULLIF(ss_p2.cumpct, 0)

 

Dieter

You must sign in to leave a comment.