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

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:
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.

Is there a way to string the select statement together without building a cursor?

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 , '' )
...