Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Posts
    12

    Unanswered: an interesting one :)

    I'm currently working on an application (in Java) which writes to an Oracle (8i) database. I'm then creating views from this, from which I can view reports.

    I'm having a problem in one calculation in the view, cause sometimes I get a divide by zero error. EG: a = x / y and y can sometimes be zero.

    I dont want to change the way the code works cause zero is a ligitimate answer, so what I was wondering was is there a way to create a SQL view which will get over this? Maybe something like:-
    if y = zero, then output zero for a,
    else a = x / y

    Any ideas?

  2. #2
    Join Date
    Oct 2002
    Location
    Plymouth UK
    Posts
    116
    What does your view look like?

  3. #3
    Join Date
    Oct 2003
    Posts
    12

    the view...

    It's simple enough, just really a create view
    select (previousView.a / previousView b) AS thisValue
    from previousview

    previousView b is the value that can sometimes be zero and causes the divide by zero error

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    use case or decode

    i.e.

    case when b=0 then 0 else a/b end


    Alan

  5. #5
    Join Date
    Oct 2002
    Location
    Plymouth UK
    Posts
    116
    This sort of thing should work

    Code:
    create view
    select decode (previousView.b, 0, previousView.b, previousView.a / previousView.b AS thisValue
    from previousview;

  6. #6
    Join Date
    Oct 2003
    Posts
    12

    perfect

    that worked cheers!

Posting Permissions

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