Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Location
    Charlotte, NC
    Posts
    79

    Unanswered: Checking for zero values

    Hi

    I have a query that is almost working, but I am having a problem with stoping a divide by 0 problem. The query is:

    select
    sum(tot_ln_am/tot_ln_qt) total_us,
    sum(purch_ln_am/purch_ln_qt) purchase_us,
    sum(refi_ln_am/refi_ln_qt) refi_us,
    sum(DECODE(pii.mrt_state_region_xref.region, 'Northeast', pii.mrt_state_avg_loan_am_mth.tot_ln_am/pii.mrt_state_avg_loan_am_mth.tot_ln_qt)) tot_ne,
    sum(DECODE(pii.mrt_state_region_xref.region, 'Northeast', pii.mrt_state_avg_loan_am_mth.tot_ln_am/pii.mrt_state_avg_loan_am_mth.tot_ln_qt)) purchase_ne,
    sum(DECODE(pii.mrt_state_region_xref.region, 'Northeast', pii.mrt_state_avg_loan_am_mth.tot_ln_am/pii.mrt_state_avg_loan_am_mth.tot_ln_qt)) refi_ne,
    sum(DECODE(pii.mrt_state_region_xref.region, 'Midwest', pii.mrt_state_avg_loan_am_mth.tot_ln_am/pii.mrt_state_avg_loan_am_mth.tot_ln_qt)) tot_mw,
    sum(DECODE(pii.mrt_state_region_xref.region, 'Midwest', pii.mrt_state_avg_loan_am_mth.tot_ln_am/pii.mrt_state_avg_loan_am_mth.tot_ln_qt)) purchase_mw,
    sum(DECODE(pii.mrt_state_region_xref.region, 'Midwest', pii.mrt_state_avg_loan_am_mth.tot_ln_am/pii.mrt_state_avg_loan_am_mth.tot_ln_qt)) refi_mw,
    sum(DECODE(pii.mrt_state_region_xref.region, 'South', pii.mrt_state_avg_loan_am_mth.tot_ln_am/pii.mrt_state_avg_loan_am_mth.tot_ln_qt)) tot_sh,
    sum(DECODE(pii.mrt_state_region_xref.region, 'South', pii.mrt_state_avg_loan_am_mth.tot_ln_am/pii.mrt_state_avg_loan_am_mth.tot_ln_qt)) purchase_sh,
    sum(DECODE(pii.mrt_state_region_xref.region, 'South', pii.mrt_state_avg_loan_am_mth.tot_ln_am/pii.mrt_state_avg_loan_am_mth.tot_ln_qt)) refi_sh,
    sum(DECODE(pii.mrt_state_region_xref.region, 'West', pii.mrt_state_avg_loan_am_mth.tot_ln_am/pii.mrt_state_avg_loan_am_mth.tot_ln_qt)) tot_wt,
    sum(DECODE(pii.mrt_state_region_xref.region, 'West', pii.mrt_state_avg_loan_am_mth.tot_ln_am/pii.mrt_state_avg_loan_am_mth.tot_ln_qt)) purchase_wt,
    sum(DECODE(pii.mrt_state_region_xref.region, 'West', pii.mrt_state_avg_loan_am_mth.tot_ln_am/pii.mrt_state_avg_loan_am_mth.tot_ln_qt)) refi_wt
    from pii.mrt_state_avg_loan_am_mth, pii.mrt_state_region_xref
    where pii.mrt_state_avg_loan_am_mth.st_nm = pii.mrt_state_region_xref.st_nm

    What I would like to do is to check for 0 values in the divisor so that the calcualtion is not performed.

    I would appreciate any help that anyone can give me.

    TIA

    Michael

  2. #2
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    You can use DECODE function for the devisor.

    for example in your query, You can formulate the second line like following.

    select
    sum(tot_ln_am/DECODE (tot_ln_qt, 0, 1, tot_ln_qt)) total_us,


    You can select any other values in place of 1 in the above example. Make sure the value you select should not affect the required result. You have to select the values accordingly.
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

Posting Permissions

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