Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2007
    Posts
    29

    Unanswered: remove zeros rows from table

    Hi all,
    I am working with oracle 10g database.
    I have a table of with three columns of varchar2 datatype and four columns of number datatype.
    In the table there are rows where all the four numeric fields are 0
    i.e looking at a row of record, the value of the four numeric fields is 0.
    I want to extract out or view only rows with at least one of the four numeric fields not equal to zero. I dont want to view the records having the four numeric fields all equal to zero.
    Please can some one help me out with regards to the right SQL query to be used
    Thanks
    'soga

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Code:
    select * from my_table
    where nvl(col1,0) <> 0 or nvl(col2,0) <> 0 or nvl(col3,0) <> 0 or nvl(col4,0) <> 0
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Nov 2007
    Posts
    29

    remove zeros rows from table

    Thanks but this query does not seem to solve the problem
    the Or clause will select rows where at least one numeric value field has a zero. I am interested in excluding rows where all the four numeric fields each have 0 (zero) stored as the database value i.e


    field1 field2 field3 field4

    0 0 0 0

    0 0 7 0.


    I am interested in excluding row1 and not row2
    thanks

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    Code:
    select * from my_table
    where (nvl(col1,0) + nvl(col2,0) + nvl(col3,0) + nvl(col4,0) > 0)
    ?
    --=cf

    -- edit: actually, this doesn't work, as in the case where col1 = 3, col2 = -3, col3 = 0, col4 = 0, total is 0
    Last edited by chuck_forbes; 03-12-08 at 11:42.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    I think you are thinking of "AND", instead of "OR". Thanks to Mr. DeMorgan, the above could be rewritten as
    Code:
    select * from my_table
    where not (nvl(col1,0) = 0 and nvl(col2,0) = 0 and nvl(col3,0) = 0 and nvl(col4,0) = 0)
    Both should give identical results.

  6. #6
    Join Date
    Nov 2007
    Posts
    29

    remove zeros rows from table

    Hi,
    I think this should work fine.
    I tried it earlier before contacting the forum but was not sure cos i'm dealing
    with rows of about 1million records and there is no way i can manually check.
    I just hope it works fine.
    Thanks guys, you are doing great at the forum.

Posting Permissions

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