Re: SQL select using where in

Hi Chuck, et al.

This is mostly for getting something into the archives. It's an idea I got when looking at this question.

The use of separate host variables is fine, so long as you always have the same number of values to check. Or if you have enough host variables to handle the maximum number of such values. I think it'd work to have values that are all blanks when you are testing only 2 but could have up to 5, say. The test would still succeed, because you'd match one of the items - IN is effectively a set of OR operators.

The alternative of dynamic SQL allows for any number of values, of course.

Regards
Vern

On 2/12/2013 1:32 PM, CRPence wrote:

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