Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2014
    Posts
    10

    Question Answered: Want to Skip the Row coming in result set on Division of Zero error.

    in my procedure i am getting the division of zero error. So I used NullIF for handling division of zero that results me row with null value on that column.

    but I don't want that row to come in result set? I want to skip that row from my result set ?

    What is possible solution for that?

  2. Best Answer
    Posted by mark.b

    "
    Code:
    select ...
    from (
      select
      case
        when  ... then .../nullif(expr1, 0) ...
        when  ... then .../nullif(expr2, 0) ...
      end as kpi_value
      ...
      where ...
    )
    where kpi_value is not null
    "


  3. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Code:
    select 1./i
    from table(values 0,1,2) t(i)
    where nullif(i, 0) is not null
    If it's not a solution for your problem, then describe the problem more precisely.
    Regards,
    Mark.

  4. #3
    Join Date
    Oct 2014
    Posts
    10
    Click image for larger version. 

Name:	1.JPG 
Views:	8 
Size:	49.4 KB 
ID:	16575

    A Sample code is above.

    If I have many column in Denominator any one may have 0.0 as its value , Then what should i will do for skipping row as from above method may be possible that in above example if 28 KPI_CD has 0.0 as denominator than its skipped for code 40 too. Whether that should not be skipped.

    And I cant do UNION for Each KPI_CD as i have more then 25 KPI_CD to Execute.

    Is there any solution for that?

  5. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    How about add a predicate to not get rows with 0.0???

    Code:
    AND COLUMN_WHATEVER <> 0.0
    Dave

  6. #5
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Code:
    select ...
    from (
      select
      case
        when  ... then .../nullif(expr1, 0) ...
        when  ... then .../nullif(expr2, 0) ...
      end as kpi_value
      ...
      where ...
    )
    where kpi_value is not null
    Regards,
    Mark.

Tags for this Thread

Posting Permissions

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