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