Results 1 to 14 of 14
  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: getting divisor is equal to zero...

    Hi

    I have this query but am getting 'divisor is equal to zero' for some reason:

    Code:
    select * from (
                                      
    SELECT BSC,CELL,
    
    to_char( max(decode(TRUNC(DATETIME),Trunc(SYSDATE-1),CCALLS))) as day1,
    
    FROM (
    
    select DATETIME as DATETIME,BSC as BSC ,CELL as CELL
    
    ,(TFTRALACC/TFNSCAN+THRALACC/THNSCAN)*TAVASCAN / TAVAACC
    
    from ERICSSON_BSS2.xx_CELL_MAIN_BH_DY
    
    where
    
    datetime > sysdate-1
    
    )
    
    group by bsc,cell
    
    union all
    
    select 
    
    '--',
    
    '--',
    
    to_char(sysdate-1)
    
    from 
    
    dual
    
    )
    
    order by bsc
    Will appreciate any help.

    Regards
    Shajju
    Last edited by shajju; 10-01-09 at 19:04.

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    I would believe Oracle that the reason is 'divisor is equal to zero' (ORA-01476).
    As I cannot see any division in the posted query, I would examine ERICSSON_BSS2.xx_CELL_MAIN_BH_DY (suppose that it is a view).

  3. #3
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    It is possible that the round function is throwing the error, though I doubt Oracle's round function is that buggy. I guess the trunc function could, too. Division pops up in unexpected places, and if you're running tens or hundreds of millions of rows at a function it can do strange stuff.

    When debugging something like this, try a divide and conquer approach. Do tests of parts of the query to see which expressions are actually causing the error. You might also try looking at the actual data you're feeding in. And, as the other poster suggested, it would help if you could show us any dependent tables or views.

    "decode(TRUNC(DATETIME),Trunc(SYSDATE-1),CCALLS)"

    I'm more familiar with CASE, but it looks like it's basically checking whether datetime is yesterday. Given that you're then passing the result to max, you could move this into a HAVING clause.
    Last edited by sco08y; 09-30-09 at 22:36.

  4. #4
    Join Date
    Aug 2008
    Posts
    464
    OK, there was a slight mistake in the query. I've changed it now.
    Basically, instead of the formula for CCALLS, it should be:

    (TFTRALACC/TFNSCAN+THRALACC/THNSCAN)*TAVASCAN / TAVAACC

    Here you can see the division happening.

    I was hoping this formula would give you an indication as to why I'm getting divisor is equal to zero.

    If I use just this formula in a simple select query, the result is like 0.02. But if I use this formula as part of the query posted here, I get divisor is equal to zero.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I use this formula as part of the query posted here, I get divisor is equal to zero.

    The error occurs when TFNSCAN or TAVAACC are zero.
    This is a data dependent error.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Aug 2008
    Posts
    464
    I really wish it was that simple. That's why I was confused. Values for TFNSCAN or THNSCAN or TAVAACC arenot zero.......

    As I said, if I use the equation in a simple select statement like:

    select (TFTRALACC/TFNSCAN+THRALACC/THNSCAN)*TAVASCAN / TAVAACC as TFTHT, I get a result like 0.02.

  7. #7
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by shajju
    I really wish it was that simple. That's why I was confused. Values for TFNSCAN or THNSCAN or TAVAACC arenot zero.......

    As I said, if I use the equation in a simple select statement like:

    select (TFTRALACC/TFNSCAN+THRALACC/THNSCAN)*TAVASCAN / TAVAACC as TFTHT, I get a result like 0.02.
    I really wish you provided proof there are no zeroes in that columns.
    If called from TOAD/SQL*Developer/another tool, your "simple select statement" (by the way, it is not complete) proves nothing, as it displays only a few firstly fetched rows.
    What about adding
    Code:
    WHERE TFNSCAN=0 or THNSCAN=0 or TAVAACC=0
    to that "simple select statement" and the examine results?

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Those are great column names....makes it very easy to read

    (TFTRALACC/TFNSCAN+THRALACC/THNSCAN)*TAVASCAN / TAVAACC

    And since you are using parenthesis


    (((TFTRALACC/TFNSCAN)+(THRALACC/THNSCAN))*(TAVASCAN / TAVAACC))

    Is that correct?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Quote Originally Posted by shajju
    I really wish it was that simple. That's why I was confused. Values for TFNSCAN or THNSCAN or TAVAACC arenot zero.......
    One of two realities exist.
    1) Oracle is correct & you are mistaken
    2) You are correct & have a reproducible bug.

    If I were a betting man, I place all my money on #1.

    Now prove what exists (& then fix the data.)
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by anacedent
    2) You are correct & have a reproducible bug.

    People are wondering why I'm cracking up at my desk
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Aug 2008
    Posts
    464
    Thanks guys for all your help and criticism. Flyboy's suggestion really helped. Apologies for my naive questions.

  12. #12
    Join Date
    Aug 2008
    Posts
    464
    And ofcourse Anacedent knows what to put all his money on.

    I have a slightly different situation now. Same SQL but this time bsc is of data type 'number'. So now I need to convert the data type of bsc to varchar. So I used:

    Code:
    'select to_char(bsc)'
    instead of simply
    Code:
    'select bsc'
    but then I'm getting an error for the end of the query where I'm 'ordering by bsc'.
    Q: If I change the data type of bsc using to_char, is this equal to making it 'varchar'?

  13. #13
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    SELECT to_char(bsc) AS bsc
    ORDER BY bsc
    -- or ORDER BY 1

  14. #14
    Join Date
    Aug 2008
    Posts
    464

    Thanks.

    Thanks........

Posting Permissions

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