Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2009
    Posts
    2

    Unanswered: "Variable" where clause?

    Hello

    Not sure if using case will work here. We have an input file with, say 11 fields, 3 of which are the key, 8 optional. We want to right one single statement to query the DB, however, any of the 8 optional could be blank on the input. Say on one record, ssn is blank, on another its populated. Is it possible to code a select statement that would have the where ssn = if its populated, but not read if ssn is blank?

    Don't really want a huge if statement with as many selects written

    thanks

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    NattyLight, there are a few ways this might be done.

    One is to build the query string with if statements used to add the other Where clauses as needed. Then run it dynamically.

    Another is to do something like this:
    Code:
    SELECT columns
    FROM table-name
    WHERE Key_Col1 = variable
      AND Key_Col2 = variable
      AND Key_Col3 = variable
      AND Opt_Char_Col1 LIKE variableC || '%'
      AND Opt_Numeric_Col1 BETWEEN COALESCE(variableN,0) AND COALESCE(VariableN,max-value)
    This assumes the Character variableC is an empty string ( '' ) is you don't want to filter on it or a value if you do.
    Also, the Numeric VariableN is a NULL if you don't want to filter on it or an actual value if you do.

    I might come up with a third way if neither of these work.

  3. #3
    Join Date
    Jun 2009
    Posts
    2
    Is there a way to string the select statement together without building a cursor?
    Last edited by NattyLight; 06-12-09 at 14:36.

  4. #4
    Join Date
    Dec 2005
    Posts
    273
    a constuct like this might be possible:

    WHERE
    ...
    AND ( optional_column_1 = :read_value1 OR :read_value1 = '' )
    AND ( optional_column_2 = :read_value2 OR :read_value2 = '' )
    ...

    or this one:

    ...
    AND :read_value1 IN ( optional_column_1 , '' )
    AND :read_value2 IN ( optional_column_2 , '' )
    ...
    Last edited by umayer; 06-15-09 at 03:09.

Posting Permissions

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