1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

decode function not returns "0" for 0/0

Discussion in 'SQL PL/SQL' started by UmaJanaa, Jun 25, 2009.

  1. UmaJanaa

    UmaJanaa Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Code (SQL):

    SELECT DISTINCT commonpackage.get_communityname (communitycode),
                    SUM (leads) leads, SUM (leadstotours) leadstours,
                    SUM (tourstomovein) toursmoveins,
                    SUM (leadstomovein) leadsmoveins,
                    SUM (leads) || ':' || SUM (leadstotours) leadstotours,
                    SUM (leadstotours) || ':'
                    || SUM (tourstomovein) tourstomoveins,
                    SUM (leads) || ':' || SUM (leadstomovein) leadstomoveins,
                    ROUND (  SUM (leadstotours)
                           / DECODE (SUM (leads), 0, NULL, SUM (leads))
                           * 100,
                           2
                          ) percleadstotours,         -----> in this 4/0 returns 0
                    ROUND (  SUM (tourstomovein)
                           / DECODE (SUM (leadstotours),
                                     0, NULL,
                                     SUM (leadstotours)
                                    )
                           * 100,
                           2
                          ),
                        -----> in this 0/0 doesn't returns 0, that column is blank
                    ROUND (  SUM (leadstomovein)
                           / DECODE (SUM (leads), 0, NULL, SUM (leads))
                           * 100,
                           2
                          ) percleadstomoveins        -----> in this 4/0 returns 0
               FROM (SELECT   communitycode, COUNT (*) leads, 0 leadstotours,
                              0 tourstomovein, 0 leadstomovein
                         FROM leads
                        WHERE leaddate BETWEEN '01-Jun-2009' AND '30-Jun-2009'
                     GROUP BY communitycode
                     UNION ALL
                     SELECT   communitycode, 0, COUNT (leadid) leadstotours,
                              0 tourstomovein, 0 leadstomovein
                         FROM leadactivities
                        WHERE activityname LIKE '%Tour%'
                          AND leadid IN (
                                 SELECT leadid
                                   FROM leads
                                  WHERE leaddate BETWEEN '01-Jun-2009'
                                                     AND '30-Jun-2009')
                     GROUP BY communitycode
                     UNION ALL
                     SELECT   communitycode, 0, 0, COUNT (*), 0 tourstomovein
                         FROM leadactivities
                        WHERE activityname LIKE '%Tour%'
                          AND leadid IN (
                                 SELECT leadid
                                   FROM leads
                                  WHERE leaddate BETWEEN '01-Jun-2009'
                                                     AND '30-Jun-2009'
                                    AND STATUS = 'Moved-In')
                     GROUP BY communitycode
                     UNION ALL
                     SELECT   communitycode, 0, 0, 0, COUNT (*) leadstomovein
                         FROM leads
                        WHERE leaddate BETWEEN '01-Jun-2009' AND '30-Jun-2009'
                          AND STATUS = 'Moved-In'
                     GROUP BY communitycode) a
           GROUP BY communitycode
     
    plz help me to resolve in that issue... am expecting ur reply... thanks in advance...
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Well I don't write sql much but if this code is the one which is not giving expected reult you should try it with real values and select it from dual. Something like

    Code (SQL):
    SELECT ROUND (  SUM (tourstomovein)
                           / DECODE (SUM (leadstotours),
                                     0, NULL,
                                     SUM (leadstotours)
                                    )
                           * 100,
                           2
                          ) FROM dual
    Replace by actual values and see if what's the problem
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Code (SQL):

    SQL> SELECT ROUND(4/DECODE(0,0,NULL,0)*100,2) FROM DUAL;

    ROUND(4/DECODE(0,0,NULL,0)*100,2)
    ---------------------------------


    SQL> SELECT ROUND(0/DECODE(0,0,NULL,0)*100,2) FROM DUAL;

    ROUND(0/DECODE(0,0,NULL,0)*100,2)
    ---------------------------------


    SQL> SELECT ROUND(0/DECODE(0,0,0,0)*100,2) FROM DUAL;
    SELECT ROUND(0/DECODE(0,0,0,0)*100,2) FROM DUAL
                  *
    ERROR at line 1:
    ORA-01476: divisor IS equal TO zero

     
    and these are correspondigly equal to...

    Code (SQL):

    SQL> SELECT ROUND(4/NULL*100,2) FROM DUAL;

    ROUND(4/NULL*100,2)
    -------------------


    SQL> SELECT ROUND(0/NULL*100,2) FROM DUAL;

    ROUND(0/NULL*100,2)
    -------------------


    SQL> SELECT ROUND(0/0*100,2) FROM DUAL;
    SELECT ROUND(0/0*100,2) FROM DUAL
                  *
    ERROR at line 1:
    ORA-01476: divisor IS equal TO zero


    SQL>
     
    Btw, 0/0 is neither 1 or 0 .. its an error.