locked
IS NULL AND 0 SELECT STATMENT RRS feed

  • Question

  • ID SALE MONEY COMP RENT YEAR
    11 0.00 1 NULL 1 2007
    11 NULL 0 2 2 2007
    11 0.00 NULL 0.00 NULL 2007
    11 NULL NULL NULL NULL 2008
    11 0.00 NULL 1 NULL 2008
    11 4 5 0.00 0.00 2008
    12 0.00 4 NULL 6 2007
    12 NULL 0 7 2 2007
    12 0.00 NULL 0.00 8 2007
    12 NULL NULL NULL NULL 2008
    12 5 NULL 1 4 2008
    12 0.00 5 22 0.00 2008
         
         
         
     I like to  have below table
         
    ID SALE MONEY COMP RENT YEAR
    11 0.00 1 NULL 1 2007
    11 NULL 0 2 2 2007

     

    MY SELECT STATMENT IS NOT WORKING DON'T KNOW WHY, DATA TYPE IS NUMERIC

    ANY HELP WILL APPRECIATE

    SELECT DISTINCT
     SALE,
     MONEY,
     COMP,
     RENT
     FROM TABLE
     WHERE ID = 11 AND YEAR = 2007
     AND SALE IS NOT NULL AND SALE <> 0
     OR MONEY IS NOT NULL AND MONEY <> 0


    simam

    Friday, May 25, 2012 1:06 AM

Answers

  • hi,

    try this

    SELECT DISTINCT
     SALE,
     MONEY,
     COMP,
     RENT
     FROM TABLE
     WHERE (ID = 11 AND YEAR = 2007)
     AND (ISNULL(SALE,0) <> 0)
     OR ISNULL(MONEY,0) <> 0
     OR ISNULL(COMP ,0) COMP <> 0
     OR ISNULL(RENT ,0) <> 0)

    Regards
    Satheesh

    Friday, May 25, 2012 2:20 AM

All replies


  • SELECT DISTINCT
     SALE,
     MONEY,
     COMP,
     RENT
     FROM TABLE
     WHERE ID = 11 AND YEAR = 2007
     AND SALE IS NOT NULL AND SALE <> 0
     OR MONEY IS NOT NULL AND MONEY <> 0
     OR COMP IS NOT NULL AND COMP <> 0
     OR RENT IS NOT NULL AND RENT <> 0

    simam

    Friday, May 25, 2012 1:07 AM
  • When you have both AND and OR in your where clause you must put the correct conditions into parenthesis, so to fix your first query it will be:

    SELECT DISTINCT
     SALE,
     MONEY,
     COMP,
     RENT
     FROM TABLE
     WHERE ID = 11 AND YEAR = 2007
     AND ((SALE IS NOT NULL AND SALE <> 0)
     OR (MONEY IS NOT NULL AND MONEY <> 0))


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Friday, May 25, 2012 1:16 AM
  • Try this

    SELECT DISTINCT
     SALE,
     MONEY,
     COMP,
     RENT
     FROM TABLE
     WHERE ID = 11 AND YEAR = 2007
     AND (SALE IS NOT NULL AND SALE <> 0
     OR MONEY IS NOT NULL AND MONEY <> 0
     OR COMP IS NOT NULL AND COMP <> 0
     OR RENT IS NOT NULL AND RENT <> 0);


    Many Thanks & Best Regards, Hua Min

    Friday, May 25, 2012 1:20 AM
  •  

    Hua- Thank you, yours is working

    Noami- Apprecaite your help for some reason when I try your way it doesn't work.


    simam

    Friday, May 25, 2012 1:33 AM
  • It should work but I didn't add two extra conditions for COMP and RENT. Please add them also. My main post was to apply () for conditions.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Friday, May 25, 2012 2:01 AM
  • hi,

    try this

    SELECT DISTINCT
     SALE,
     MONEY,
     COMP,
     RENT
     FROM TABLE
     WHERE (ID = 11 AND YEAR = 2007)
     AND (ISNULL(SALE,0) <> 0)
     OR ISNULL(MONEY,0) <> 0
     OR ISNULL(COMP ,0) COMP <> 0
     OR ISNULL(RENT ,0) <> 0)

    Regards
    Satheesh

    Friday, May 25, 2012 2:20 AM
  • What if you wanted to exclude all 0 values but keep NULL values and all other non 0 values?
    Monday, June 25, 2012 10:00 PM