Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2004
    Posts
    13

    Question Help - ORA-01476: divisor is equal to zero

    Hi all. I need help. When running the query below (in SQL*Plus - Oracle 8), I receive this error: ERROR:
    ORA-01476: divisor is equal to zero.



    PHP Code:
    select substr(to_char(add_months(g.trans_date,3),'YY'),1,2fy,
        
    g.reporting_cdsubstr(r.reporting_cd_desc,1,35Description,
        
    sum(decode(g.trans_cd,'R1',1,'R2',1,'A01',-1)) num_collections,
        (
    sum(DEBIT_AMOUNT SUM(CREDIT_AMOUNT)) BALANCE
        (
    sum(distinct Credit_amount) / max(debit_amount))* (100.00percent
    p.Cntrl_no,
    Gl_proc_cd,
    Total_amount
    from gen_ledger g
    perm_bill prpt_code r
    where g
    .reporting_cd r.reporting_cd
    and g.cntrl_no p.cntrl_no
    and p.cntrl_no like 'TEST%'
    and (trunc(g.trans_date) >= to_date('05-OCT-2002','DD-MON-YYYY')
    and 
    trunc(g.trans_date) <= to_date('30-SEP-2006','DD-MON-YYYY'))
    and 
    g.trans_cd in ('01','R1','R2','A01','REF','ARF')
    group by substr(to_char(add_months(g.trans_date,3),'YY'),1,2), 
         
    p.cntrl_nog.reporting_cdr.reporting_cd_desctotal_amountgl_proc_cd
    Are there any suggestions on how can I get around the ORA-01476: divisor is equal to zero error?

    Thank you in advance.

  2. #2
    Join Date
    May 2004
    Location
    BA [ARG]
    Posts
    137
    Try with something like this:

    PHP Code:
    select substr(to_char(add_months(g.trans_date,3),'YY'),1,2fy,
        
    g.reporting_cdsubstr(r.reporting_cd_desc,1,35Description,
        
    sum(decode(g.trans_cd,'R1',1,'R2',1,'A01',-1)) num_collections,
        (
    sum(DEBIT_AMOUNT) &#8211; SUM(CREDIT_AMOUNT)) BALANCE, 
        
    (sum(distinct Credit_amount) / decode(nvl(max(debit_amount), 0), 01max(debit_amount)))* (100.00percent
    p.Cntrl_no,
    Gl_proc_cd,
    Total_amount
    from gen_ledger g
    perm_bill prpt_code r
    where g
    .reporting_cd r.reporting_cd
    and g.cntrl_no p.cntrl_no
    and p.cntrl_no like 'TEST%'
    and (trunc(g.trans_date) >= to_date('05-OCT-2002','DD-MON-YYYY')
    and 
    trunc(g.trans_date) <= to_date('30-SEP-2006','DD-MON-YYYY'))
    and 
    g.trans_cd in ('01','R1','R2','A01','REF','ARF')
    group by substr(to_char(add_months(g.trans_date,3),'YY'),1,2), 
         
    p.cntrl_nog.reporting_cdr.reporting_cd_desctotal_amountgl_proc_cd

  3. #3
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    You can use NVL and CASE :

    PHP Code:
    select substr(to_char(add_months(g.trans_date,3),'YY'),1,2fy,
        
    g.reporting_cdsubstr(r.reporting_cd_desc,1,35Description,
        
    sum(decode(g.trans_cd,'R1',1,'R2',1,'A01',-1)) num_collections,
        (
    sum(DEBIT_AMOUNT SUM(CREDIT_AMOUNT)) BALANCE
        CASE 
    WHEN (NVL(max(debit_amount),0) = 0THEN NULL ELSE (sum(distinct Credit_amount) / max(debit_amount))* (100.00END percent
        
    p.Cntrl_no,
        
    Gl_proc_cd,
        
    Total_amount
    from gen_ledger g
    perm_bill prpt_code r
    where g
    .reporting_cd r.reporting_cd
    and g.cntrl_no p.cntrl_no
    and p.cntrl_no like 'TEST%'
    and (trunc(g.trans_date) >= to_date('05-OCT-2002','DD-MON-YYYY')
    and 
    trunc(g.trans_date) <= to_date('30-SEP-2006','DD-MON-YYYY'))
    and 
    g.trans_cd in ('01','R1','R2','A01','REF','ARF')
    group by substr(to_char(add_months(g.trans_date,3),'YY'),1,2), 
         
    p.cntrl_nog.reporting_cdr.reporting_cd_desctotal_amountgl_proc_cd
    This way if max(debit_amount) is NULL or equals to 0, percent will be NULL, else it will have the value you were calculating.

    HTH & Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  4. #4
    Join Date
    Jul 2004
    Posts
    13

    Thanks all!

    Thanks All!!!!!

    I played around with them. The NVL and CASE worked very well!

    Shelli

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •