Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2016
    Posts
    2

    Unanswered: need to omit /include conditions in where clause based on input

    Hi All,

    I have 3 inputs a, b, c (char value) which are passed on to a DB2 SQL query. Consider C is a optional input. Is there any way to make the 3rd conditional optional in a single SQL query. Currently I am using 2 sql query in my code; first one with inputs a,b,c in the WHERE clause (C has a value) & other one with inputs a, b in the WHERE clause (c does not have a value)

    I am trying to achieve the below logic ; but couldn't do it

    select * from table where COLUMN1=a AND column2=b
    (CASE WHEN C IS NOT NULL
    then AND column3=c
    ELSE ' ')
    with ur;
    ==> getting sqlcode -104 , sql state 42601

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I prefer the 2 separate queries. But if you really want to make it one, you need to learn how to use the CASE statement properly.

    Try:
    Code:
    (CASE WHEN C IS NOT NULL 
     then c
     ELSE column3 end) = column3
    Dave

  3. #3
    Join Date
    Feb 2016
    Posts
    2

    rukku

    Thanks Dave. But I don't need the condition itself if the input is not present, kindly let me know what can be done

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    ?? there has to be some condition there or you have to write your SQL dynamically. If you look closely at what I provided to you. If your C input exists it will be compared to col3, otherwise col3 is compared to itself, which is a nothing kind of check. Otherwise, you can do the same type of thing with something like:

    Code:
    and (c = column3
       or c is null)
    either way is additional processing logic and why I explained earlier that it is best to have the two separate queries.
    Dave

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
  •