Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2005
    Posts
    80

    Unanswered: Build a sql query

    Hi ,

    Could you please help me building a query with the below requirements.

    Table1 has got 5 columns.
    (PK)col1 col2 col3 col4 col5
    1 a b c d
    2 e f h
    3 I k l
    4 m n o p
    5 q r s t

    I need to have one single query where in i would have to compare compare against all the columns, if a value is null then still that row should be be fetched. like

    select * from table1 where col1=1 and/or col2=a and/or col3=b and/or col4=c and/or col5=""

    the first row should be displayed. like if one value is missing then that value has to be ignored and others has to be conisidered. I know i can do that with the programming logic. But i need to find out a way doing it with a sql query.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I am not sure I understand what you want to return. Could you be more specific and supply some examples of what you expect to see from what you have?

    Andy

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    If I got this right (pending the requested explanation), I think you just have to do this:

    SELECT ...
    FROM ...
    WHERE col1 = 1 AND ( col2 IS NULL OR col2 = ... ) AND ( col3 IS NULL OR col3 = ... ) AND ...

    p.s: Read up on the semantics of NULL and how it is used in predicates.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I do not think that that is what is desired. But if it is, then ther is even an easier way:

    select * from table1 where col1 = 1 and coalesce(col2,'a') = 'a' and coalesce(col3,'b') = 'b' and ...

    Andy

  5. #5
    Join Date
    May 2006
    Posts
    82
    the first row should be displayed. like if one value is missing then that value has to be ignored and others has to be conisidered. I know i can do that with the programming logic. But i need to find out a way doing it with a sql query.
    like if one value is missing then that value has to be ignored and others has to be conisidered.............?????

    Do you mean to say if the value is missing or mismatching then that column should be eliminated from the result set...? (** dont think this is a poor question, just need to confirm what you need)

    OR
    Did you mean to say that particular row should be eliminated from the result set..?

    Is this query STATIC (will you embedd in program ..?)

Posting Permissions

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