AW: Using host variable that contains a list of customer# in the sql INclause

Hi,

you neither can use arrays, nor array elements in your select statements.
It's also not possible, to use a single Hostvariable containing n elements
in an IN-predicate.

But there are different solutions:
1. Datastructure and Array
   You have to overlay the array with an datastructure containing all
elements as subfields.
   In your select statement, you have to specify all elements.
   If not all elements are used, initialize the unused elements with a value
that does not exist (i.e. *HIVAL)

D DSCustomer      DS
D   Customer1                         like(Customer)
D   Customer2                         like(Customer)
...
D   Customer10                        like(Customer)
D myCustomer                          like(customer) dim(10)
D                                     overlay(DSCustomer)
 *--------------------------------------------------------------
C/exec sql
C+ declare mycursor scroll cursor for
C+   select *
C+    from myfile
C+    where customer in (:Customer1, :Customer2, ... Customer10)
C/exec sql

2. String
Concat all your Custumer in a string (removing all leading and trailing
blanks) and use a like predicate.
Please consider I added a comma at the beginning and the end of the string.
(This may result in a bad performance, because no index can be used for the
customer column can be used!)

D myCustomer      s            256    varying inz(',12345,12346,12350,')
 *-----------------------------------------------------------------------
C/exec sql
C+ declare mycursor scroll cursor for
C+   select *
C+    from myfile
C+    where :myCustomer like '%,' concat trim(Customer) concat ',%'
C/exec sql

Birgitta

-----Ursprungliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]Im Auftrag von Lim Hock-Chai
Gesendet: Donnerstag, 29. Dezember 2005 19:17
An: RPG programming on the AS400 / iSeries
Betreff: Using host variable that contains a list of customer# in the
sql INclause


Other than using dynamic sql or write myCustomer data to a temp file and
join them,
anybody know if it is possible to use array or char string in the IN
clause like below?

D myCustomer      s                   like(customer) dim(10)  *** or ***
D myCustomer      s            256    varying inz('12345,12346')

C/exec sql
C+ declare mycursor scroll cursor for
C+   select *
C+    from myfile
C+    where customer in (:myCustomer)
C/exec sql



--
This is the RPG programming on the AS400 / 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.