Re: SQL select using where in

On 12 Feb 2013 10:50, Rettig, Roger wrote:

I have a SQL clause embedded in a RPG program. The statement inserts
records directly into a file. In the where clause, I have an 'in'
statement using a variable:
Where pypmtt in :CheckType

FWiW, that is the "IN predicate". The SQL "IN" is not a statement; the string "IN ..." can not be executed\opened.

using debug the value of checktype = ('CK', 'BT'). This inserts 0
records.

If I hardcode and replace checktype with ('CK', 'BT'), the program
inserts the the correct records. There are other variables in the
select statement that are not causing issues.
What am I doing wrong?

The predicate as coded, best I can infer, is:
WHERE pypmtt = ('(''CK'', ''BT'')')

Effectively that predicate is asking for the rows to match a string that could not possibly match. And FWiW the SQLSTATE likely records a warning to the effect that "comparison values may never compare equal" which implies that the comparison value is /longer/ than the data type.

Basically what is required instead, is to code the following, where the CkTyp## variables are compatible with the column pypmtt:
WHERE pypmtt IN (:CkTyp01, :CkTyp02)

What is returned from the following input used for a Google web search will likely prove helpful:
"in predicate" "host variables" site:archive.midrange.com