ORA-01476: divisor is equal to zero

[memarkiam]

I'm getting the above error in a query.

How can I 'trap' this error, and basically ignore it?

Many thanks,
Mark

 

[Mockenrue]

declare
e_zero_divide exception;
pragma exception_init (e_zero_divide, -1476);
begin
<your code>
exception
when e_zero_divide then
null;
end;

 

[stefanhei]

or in your query:

... x / decode(yourDivisor,0,null,yourDivisor) ...

Stefan

 

[memarkiam]

Thanks very muchn for the quick repsonses!

Unfortunatly, This seems to generate new errors. Is it maybe becouse I'm writing a query (view) rather than a procedure.

If I can show you my SLQ (rather long, sorry), this may explain. I've wrapped my SWL in the first sugestion, but then I just get:
&quot;Error on line 0
declare
e_zero_divide exception;
pragma exception_init (e_zero_divid

ORA-06550: line 5, column 1:
PLS-00103: Encountered the symbol &quot;CREATE&quot; when expecting one of the following:

begin declare exit for goto if loop mod null pragma raise
return select update while <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall
<a single-quoted SQL string>&quot;

I'm not sure how I would use the 2nd suggestion in my sql.

declare
e_zero_divide exception;
pragma exception_init (e_zero_divide, -1476);
BEGIN
CREATE OR REPLACE VIEW ML_CRM2003 ( CUSTOMER_ID,
CUSTOMER_NO, TITLE, GNDR, MS,
ADHLDRS, DPNDTS, STCRD, CAT,
WEB, TOWN, COUNTY, POSTCD,
NO_MAILINGS, LSTMAILDT, AGE, AGERANGE,
RGN, STRTDT, CHRN, SCORE,
CILECODE, SEG, FRQ, SPAN,
RCNCY, RCNCYGROUP, TOTALNET, LIFEPURCHVAL,
LIFERETURNSVAL, LIFERETAILPURCHVAL, LIFERETAILRETURNSVAL, LIFECATPURCHVAL,
LIFECATRETURNSVAL, LIFETRANSCNT, LIFERETTRANSCNT, LIFERETVISTIS,
LIFECATTRANSCNT, ORDRQTY, AVTRNCNT, VELOCITY,
AVORDVAL, INTE, LSTAMNT, PREFSTR,
CRTSTR, FRSTPURCHSTRE, FRSTPURCHVAL, FRSTPURCHDATE
) AS select customer.CUSTOMER_ID,
customer.CUSTOMER_NO,
customer.TITLE,
customer.GENDER as Gndr,
customer.MARITAL_STATUS as MS,
customer.SEGMENTATION_VALUE_A as AdHldrs,
customer.SEGMENTATION_VALUE_B as Dpndts,
customer.SEGMENTATION_FLAG_A as StCrd,
customer.SEGMENTATION_FLAG_D as Cat,
customer.SEGMENTATION_FLAG_b as web,
address.ADDRESS_4 as Town,
address.ADDRESS_5 as County,
address.POST_CODE as PostCd,
customer.NO_MAILINGS,
customer.LAST_MAILING_DATE as LstMailDt,
TRUNC(MONTHS_BETWEEN(SYSDATE, customer.LANDMARK_DATE_A)/12,0) AS Age,
CASE
WHEN TRUNC(MONTHS_BETWEEN(SYSDATE, customer.LANDMARK_DATE_A)/12,0) <=19 then '0-19 '
WHEN TRUNC(MONTHS_BETWEEN(SYSDATE, customer.LANDMARK_DATE_A)/12,0) > 19 and TRUNC(MONTHS_BETWEEN(SYSDATE, customer.LANDMARK_DATE_A)/12,0) <= 24 then '20-24'
WHEN TRUNC(MONTHS_BETWEEN(SYSDATE, customer.LANDMARK_DATE_A)/12,0) >= 25 and TRUNC(MONTHS_BETWEEN(SYSDATE, customer.LANDMARK_DATE_A)/12,0) <= 29 then '25-29'
WHEN TRUNC(MONTHS_BETWEEN(SYSDATE, customer.LANDMARK_DATE_A)/12,0) >= 30 and TRUNC(MONTHS_BETWEEN(SYSDATE, customer.LANDMARK_DATE_A)/12,0) <= 34 then '30-34'
WHEN TRUNC(MONTHS_BETWEEN(SYSDATE, customer.LANDMARK_DATE_A)/12,0) >= 35 and TRUNC(MONTHS_BETWEEN(SYSDATE, customer.LANDMARK_DATE_A)/12,0) <= 39 then '35-39'
WHEN TRUNC(MONTHS_BETWEEN(SYSDATE, customer.LANDMARK_DATE_A)/12,0) >= 40 and TRUNC(MONTHS_BETWEEN(SYSDATE, customer.LANDMARK_DATE_A)/12,0) <= 44 then '40-44'
WHEN TRUNC(MONTHS_BETWEEN(SYSDATE, customer.LANDMARK_DATE_A)/12,0) >=45 then '45+ '
else ''
End as AgeRange,
case
when ML_POPDENS.DENSITY <= 500 then 'Rural'
when ML_POPDENS.DENSITY >= 3000 then 'Urban'
else 'Suburban'
end as Rgn,
customer.CREATE_DATE as StrtDt,
ml_churn.CHRN,
ml_rfmx_5.SCORE,
ml_rfmx_5.CILECODE,
CASE
WHEN ml_rfmx_5.SCORE <0.4 and TRUNC((CUSTOMER_LIFETIME_TOTALS.AMOUNT_1-CUSTOMER_LIFETIME_TOTALS.AMOUNT_2)/MONTHS_BETWEEN(SYSDATE, customer.CREATE_DATE),2) >=28 then '1a '
WHEN ml_rfmx_5.SCORE <0.4 and TRUNC((CUSTOMER_LIFETIME_TOTALS.AMOUNT_1-CUSTOMER_LIFETIME_TOTALS.AMOUNT_2)/MONTHS_BETWEEN(SYSDATE, customer.CREATE_DATE),2) <=28 then '1b '
WHEN ml_rfmx_5.SCORE >=0.4 and ml_rfmx_5.SCORE <0.5 then '2'
WHEN ml_rfmx_5.SCORE >=0.5 and ml_rfmx_5.SCORE <0.6 then '3'
WHEN ml_rfmx_5.SCORE >=0.6 and ml_rfmx_5.SCORE <0.7 then '4'
WHEN ml_rfmx_5.SCORE >=0.7 and ml_rfmx_5.SCORE <0.8 then '5'
WHEN ml_rfmx_5.SCORE >=0.8 and ml_rfmx_5.SCORE <0.9 then '6'
WHEN ml_rfmx_5.SCORE >=0.9 and ml_rfmx_5.SCORE <1 then '7'
else '8'
End as Seg,

CUSTOMER_LIFETIME_TOTALS.count_1 as Frq,

CASE
WHEN TRUNC(MONTHS_BETWEEN(SYSDATE, customer.CREATE_DATE),0) <= 3 then '0_3'
WHEN TRUNC(MONTHS_BETWEEN(SYSDATE, customer.CREATE_DATE),0) > 3 and TRUNC(MONTHS_BETWEEN(SYSDATE, customer.CREATE_DATE),0) <= 6 then '4_6'
WHEN TRUNC(MONTHS_BETWEEN(SYSDATE, customer.CREATE_DATE),0) > 6 and TRUNC(MONTHS_BETWEEN(SYSDATE, customer.CREATE_DATE),0) <= 9 then '7_9'
WHEN TRUNC(MONTHS_BETWEEN(SYSDATE, customer.CREATE_DATE),0) > 9 and TRUNC(MONTHS_BETWEEN(SYSDATE, customer.CREATE_DATE),0) <= 12 then '10_12'
WHEN TRUNC(MONTHS_BETWEEN(SYSDATE, customer.CREATE_DATE),0) > 12 and TRUNC(MONTHS_BETWEEN(SYSDATE, customer.CREATE_DATE),0) <= 18 then '13_18'
WHEN TRUNC(MONTHS_BETWEEN(SYSDATE, customer.CREATE_DATE),0) > 18 and TRUNC(MONTHS_BETWEEN(SYSDATE, customer.CREATE_DATE),0) <= 24 then '19_24'
WHEN TRUNC(MONTHS_BETWEEN(SYSDATE, customer.CREATE_DATE),0) > 24 then '25_'
END as Span,

CUSTOMER_LIFETIME_TOTALS.OCCURRENCE_1_TRANSACTION_DATE as Rcncy,
CASE
WHEN TRUNC(30*MONTHS_BETWEEN(SYSDATE, CUSTOMER_LIFETIME_TOTALS.OCCURRENCE_1_TRANSACTION_DATE),0) <= 30 then 'Last Month'
WHEN TRUNC(30*MONTHS_BETWEEN(SYSDATE, CUSTOMER_LIFETIME_TOTALS.OCCURRENCE_1_TRANSACTION_DATE),0) >= 31 and TRUNC(30*MONTHS_BETWEEN(SYSDATE, CUSTOMER_LIFETIME_TOTALS.OCCURRENCE_1_TRANSACTION_DATE),0) < 90 then '2-3 Months'
WHEN TRUNC(30*MONTHS_BETWEEN(SYSDATE, CUSTOMER_LIFETIME_TOTALS.OCCURRENCE_1_TRANSACTION_DATE),0) >= 91 and TRUNC(30*MONTHS_BETWEEN(SYSDATE, CUSTOMER_LIFETIME_TOTALS.OCCURRENCE_1_TRANSACTION_DATE),0) < 180 then '4-6 Months'
WHEN TRUNC(30*MONTHS_BETWEEN(SYSDATE, CUSTOMER_LIFETIME_TOTALS.OCCURRENCE_1_TRANSACTION_DATE),0) >= 181 and TRUNC(30*MONTHS_BETWEEN(SYSDATE, CUSTOMER_LIFETIME_TOTALS.OCCURRENCE_1_TRANSACTION_DATE),0) < 270 then '6-9 Months'
WHEN TRUNC(30*MONTHS_BETWEEN(SYSDATE, CUSTOMER_LIFETIME_TOTALS.OCCURRENCE_1_TRANSACTION_DATE),0) >= 271 then '9+ Months'
END as RcncyGroup,
(CUSTOMER_LIFETIME_TOTALS.AMOUNT_1-CUSTOMER_LIFETIME_TOTALS.AMOUNT_2) as TotalNet,
CUSTOMER_LIFETIME_TOTALS.AMOUNT_1 as LifePurchVal,
CUSTOMER_LIFETIME_TOTALS.AMOUNT_2 as LifeReturnsVal,
CUSTOMER_LIFETIME_TOTALS.AMOUNT_3 as LifeRetailPurchVal,
CUSTOMER_LIFETIME_TOTALS.AMOUNT_4 as LifeRetailReturnsVal,
CUSTOMER_LIFETIME_TOTALS.AMOUNT_6 as LifeCatPurchVal,
CUSTOMER_LIFETIME_TOTALS.AMOUNT_7 as LifeCatReturnsVal,
CUSTOMER_LIFETIME_TOTALS.COUNT_1 as LifeTransCnt,
CUSTOMER_LIFETIME_TOTALS.COUNT_2 as LifeRetTransCnt,
CUSTOMER_LIFETIME_TOTALS.COUNT_3 as LifeRetVistis,
CUSTOMER_LIFETIME_TOTALS.COUNT_4 as LifeCatTransCnt,
CUSTOMER_LIFETIME_TOTALS.COUNT_3+CUSTOMER_LIFETIME_TOTALS.COUNT_4 as OrdrQty,
trunc(CUSTOMER_LIFETIME_TOTALS.count_1/MONTHS_BETWEEN(SYSDATE, customer.CREATE_DATE)*12,0) as AvTrnCnt,
TRUNC((CUSTOMER_LIFETIME_TOTALS.AMOUNT_1-CUSTOMER_LIFETIME_TOTALS.AMOUNT_2)/MONTHS_BETWEEN(SYSDATE, customer.CREATE_DATE),2) AS Velocity,
TRUNC((CUSTOMER_LIFETIME_TOTALS.AMOUNT_1-CUSTOMER_LIFETIME_TOTALS.AMOUNT_2)/(CUSTOMER_LIFETIME_TOTALS.COUNT_3+CUSTOMER_LIFETIME_TOTALS.COUNT_4),2) as AvOrdVal,
CASE
WHEN TRUNC(CUSTOMER_LIFETIME_TOTALS.count_1/MONTHS_BETWEEN(SYSDATE, customer.CREATE_DATE)*12,0) = 0 then '0 '
WHEN TRUNC(CUSTOMER_LIFETIME_TOTALS.count_1/MONTHS_BETWEEN(SYSDATE, customer.CREATE_DATE)*12,0) = 1 then '1 '
WHEN TRUNC(CUSTOMER_LIFETIME_TOTALS.count_1/MONTHS_BETWEEN(SYSDATE, customer.CREATE_DATE)*12,0) = 2 then '2 '
WHEN TRUNC(CUSTOMER_LIFETIME_TOTALS.count_1/MONTHS_BETWEEN(SYSDATE, customer.CREATE_DATE)*12,0) = 3 then '3 '
WHEN TRUNC(CUSTOMER_LIFETIME_TOTALS.count_1/MONTHS_BETWEEN(SYSDATE, customer.CREATE_DATE)*12,0) = 4 then '4 '
WHEN TRUNC(CUSTOMER_LIFETIME_TOTALS.count_1/MONTHS_BETWEEN(SYSDATE, customer.CREATE_DATE)*12,0) = 5 then '5 '
WHEN TRUNC(CUSTOMER_LIFETIME_TOTALS.count_1/MONTHS_BETWEEN(SYSDATE, customer.CREATE_DATE)*12,0) = 6 then '6 '
WHEN TRUNC(CUSTOMER_LIFETIME_TOTALS.count_1/MONTHS_BETWEEN(SYSDATE, customer.CREATE_DATE)*12,0) = 7 then '7 '
WHEN TRUNC(CUSTOMER_LIFETIME_TOTALS.COUNT_1/MONTHS_BETWEEN(SYSDATE, customer.CREATE_DATE)*12,0) >= 7 then '7+ '
else ''
END as Inte,
CUSTOMER_LIFETIME_TOTALS.OCCURRENCE_1_AMOUNT as LstAmnt,
customer.STORE_NO as PrefStr,
customer.CREATE_SOURCE as CrtStr,
ml_fstp.FrstPurchStre,
ml_fstp.FrstPurchVal,
ml_fstp.FrstPurchDate
from customer, selection_906, address, CUSTOMER_LIFETIME_TOTALS, ml_popdens, ml_rfmx_5, ml_fstp, ml_churn
where selection_906.CUSTOMER_ID = customer.CUSTOMER_ID and
customer.CUSTOMER_ID = address.CUSTOMER_ID(+) and
customer.CUSTOMER_ID =+ CUSTOMER_LIFETIME_TOTALS.CUSTOMER_ID(+) and
trim(substr(address.POST_CODE,1,4)) =+ ml_popdens.PCD(+) and
customer.customer_ID =+ ml_rfmx_5.CUSTOMER_ID(+) and
customer.customer_ID =+ ml_fstp.CUSTOMER_ID(+) and
customer.CUSTOMER_ID =+ ml_churn.CUSTOMER_ID(+)
exception
when e_zero_divide then
null;
end;



Many, many thanks!!!&quot;
Mark

 

[carp]

No, you will not be able to use EXCEPTION when creating a view - EXCEPTION is PL/SQL. So you are going to have to eliminate the places in your query where a zero can crop up in a divisor. I don't think it's very likely to happen where you're dividing by months_between.

On the other hand, I see a couple of places where you are dividing by expressions like:

(CUSTOMER_LIFETIME_TOTALS.COUNT_3+CUSTOMER_LIFETIME_TOTALS.COUNT_4)

I think this is where the trouble lies. You will need to decide what to use as a default divisor here, and then do something like:

DECODE((CUSTOMER_LIFETIME_TOTALS.COUNT_3+CUSTOMER_LIFETIME_TOTALS.COUNT_4),0,1,(CUSTOMER_LIFETIME_TOTALS.COUNT_3+CUSTOMER_LIFETIME_TOTALS.COUNT_4))