Re: scriprt error

Shawn wrote:
> Hi,
>
> I run this report daily that includes the following script to perform some
> calculations.
>
> select count(distinct ev_id) eventtotal
> from invopt_acty,inv_pull,std_invopt
> where invopt_acty.ivpl_id = inv_pull.id
> and inv_pull.sdio_id=std_invopt.id
> and inv_pull.effdate =
> decode(pull_level,'G','&&unwrapped','S','&&wrapped')
> /
> select count(distinct ev_id) eventlate
> from work_glrp,event,std_invopt
> where ivpl_effdate = decode(pull_level,'G','&&unwrapped','S','&&wrapped')
> and work_glrp.sdio_id = std_invopt.id
> and work_glrp.ev_id = event.id
> and (gain_loss_rsn_code not in ('NR','NC','NT')
> or gain_loss_rsn_code is null)
> /
>
> select 'EVENTS PULLED LATE VS TOTAL EVENTS' c,
> &eventlate num,&eventtotal den,((&eventlate/&eventtotal)*100) rate
> from dual
>
>
> The problem I have is when the variable comes out to zero (which happens
> infrequently) it kills the last part of the script with this error:
>
> num,-2173941.5 den,((/-2173941.5)*100) rate
> *
> ERROR at line 6:
> ORA-00936: missing expression
>
> I can't seem to get around it. Any suggestions would be greatly
> appreciated.
>
> Shawn

Write as an anonymous block including an IF statement.

or

Determine a substitute for 0 that will work and use a DECODE to change a zero to the substittute value.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)