Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Posts
    176

    Unanswered: where clause changing based on NULL column(s)

    Hello DBAs:

    I am having difficulty constructing a where clause. I have 4 columns in a table. Col1 is never NULL. Col2,3,4 may or may not contain NULL values. Here is the situation.

    1. Col2,3,4 are NULL
    WHERE Col1 = condition

    2. Col2 is NULL, 3 may or may not be NULL. When Col3 is NOT NULL, Col4 may or may not be NULL

    where col1 and (col3(Not Null values) OR Col4(Not Null values)) = condition.

    How do I construct this logic. Please help

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I'm not sure I follow but, a Null is not equal to anything...including itself..

    Post some sample Data and what the results you expect back

    Like


    CREATE TABLE myTable99 (Col1 int, ect...

    INSERT INTO myTable99 (Col1, ect
    SELECT 1, ect UNION ALL
    SELECT 2, ect UNION ALL
    ect

    Now if the parameter is potentially nullable

    then you want

    WHERE Col2 = ISNULL(@x,Col2)

    Basically making it equal to itself...

    But yours seems to be the other way around...
    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.

  3. #3
    Join Date
    Sep 2003
    Posts
    176
    Brett,

    Let me try and re-phrase the problem. My table has 5 columns. Say these columns have to bechecked with columns of another table say B.

    1.Col1 is Never Null.

    2. If Col2 is NULL, then

    where Col1 = b.Col1

    3. If Col2 is not NULL, but Col3 is NULL

    where (Col1 = b.Col1 AND Col2 = b.Col2)

    4. If Col3 is NOT NULL,

    where (Col1 = b.Col1 AND Col2 = b.Col2)
    AND (Col3 = b.Col3 OR Col4 = b.Col4 OR Col5 = b.Col5)

    So you see the where clause differs based on whether OR not the column is NULL. How do I implement this? Can you help contructing the where clause.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yeah but you could have 5 factorial combinations, no?

    What is the business req?

    Is it because the table is denormalized? IS it hierarchal?

    I guess you could use dynamic sql...
    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.

  5. #5
    Join Date
    Sep 2003
    Posts
    176
    Yes I know I could have 5 factorial combinations. But I am concerned only with the 3 cases shown. The table is de-normalized.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How about:

    Code:
    WHERE  (Col2 is NULL AND Col1 = b.Col1)
       OR  (Col2 is not NULL AND Col3 is NULL
      AND   Col1 = b.Col1 AND Col2 = b.Col2)
       OR ((Col3 is NOT NULL AND Col1 = b.Col1 AND Col2 = b.Col2)
      AND  (Col3 = b.Col3 OR Col4 = b.Col4 OR Col5 = b.Col5))
    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.

  7. #7
    Join Date
    Sep 2003
    Posts
    176
    I think this should work. Thanks a lot.

Posting Permissions

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