Re: Embedded curosr gives "No Records" where interactive SELECT shows records

Scott Schollenberger wrote:

The value I am passing in the :WHERECLAUSE variable
contains the parentheses. However, I tried your
suggestion but it didn't work.

What does /didn't work/ mean; same zero rows, success on OPEN? To what was the variable set, when trying the given suggestion?

An embedded SQL statement with a WHERE CLAUSE using an IN test using a Host Variable(s) is for the replacement for the literal value(s) that would appear in the IN test. The :HV replaces one element in the list of values in the IN test. The given WHERE FieldName IN ('AID') would be rewritten as WHERE FieldName IN (:HV) [as noted; the suggestion]. The :HV can _not_ replace nor include the syntactical elements of the statement; i.e. :HV can not represent the entire string "('AID')" for WHERE FieldName IN :HV.

To replace the syntactical elements of the statement requires a _dynamic_ statement, whereby the variable WHERECLAUSE would be generated using WHERECLAUSE = 'WHERE FieldName IN (''' + VARNAME + ''')', and that string would be appended to the string 'SELECT ... FROM ...'

In the original case rewritten as WHERE EMCLAS IN :HV, instead of the alluded HV='(''AID'')' [as inferred from "contains the parentheses], it must only HV='AID'. Note: that is a simple string assignment, where the apostrophes are not required in the SQL statement, so it is just a normal string assignment. Given the alluded attempt to include parentheses, what may become an issue, consider that also means... do *not* use HV=''AID''.

http://publib.boulder.ibm.com/infocenter/systems/topic/sqlp/rbafywhere.htm
http://publib.boulder.ibm.com/infocenter/systems/topic/sqlp/rbafywhereexp.htm
http://publib.boulder.ibm.com/infocenter/systems/topic/sqlp/rbafymultiplewhere.htm
http://publib.boulder.ibm.com/infocenter/systems/topic/sqlp/rbafycomsrch.htm

Regards, Chuck