Re: SQL select using where in

As others already pointed out you cannot use IN and HostVariables in this
way, but ...
You can try the following instead:

Select YourColumn ....

From YourFile ...

Where :CheckType like '%' concat Trim(YourColumn) concat '%'

But keep in mind:
If you do not have other where condition this statement may/will result in
a
table scan, while when using an IN Predicate with multiple listed Host
variables the optimizer can use an index.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"


-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
Im
Auftrag von Rettig, Roger
Gesendet: Tuesday, 12.2 2013 19:51
An: RPG400-L@xxxxxxxxxxxx
Betreff: SQL select using where in

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

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?

--
Roger Rettig
Business Analyst
Cincinnati Incorporated
roger.rettig@xxxxxxxx
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.


--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.