Invalid calculation: division by zero

Database

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)

1 REPLY
Junior Contributor

Re: Invalid calculation: division by zero

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

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