Re: select into in logic

[Niels Liisberg <nli@xxxxxxxxxxxxxxxxx>]

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