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:
"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 "CREATE" 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>"
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;
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:
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.