Re: select into in logic

You can not give it a list - it will not parse the string not understand an
array - unfortunately,

1) solution
so you have to specify the complete list with host variables:

exec sql SELECT sum( AMOUNT ) into :rtnvar FROM revsprp2 WHERE CCTYPE in
( :cctypein1, :cctypein2 , :cctypein2, :cctypein3 ) and STS = ' ';


A little tric - not perfect but useful - is to replicate the first value
into unused hostvarialbes in the list. This works ok if the list is short

2) solution
another solution is to use "SPLIT" in newer TR to parser you value listlike
this:

exec sql
SELECT sum( AMOUNT )
into :rtnvar
FROM revsprp2
WHERE CCTYPE in (
select element from table ( SYSTOOLS.SPLIT (:cctypein , ',' )) a
)

that require that you :cctypein is a string where you delimit it by (here)
commas ( the last parameter)

3) solution
Finally - use a dynamic prepared statement and give the list in the
complete SQL string you prepare from

Hope it helps

Niels

On Sat, Nov 7, 2020 at 3:50 PM iseriesstuff <iseriesstuff@xxxxxxxxx> wrote:

i have the following code. when i pass a single value for the "in"
clause it works, when i try multi it doesnt.

exec sql SELECT sum( AMOUNT ) into :rtnvar FROM revsprp2 WHERE CCTYPE in
:cctypein and STS = ' ';

any ideas?

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

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com