View previous topic :: View next topic
|
Author |
Message |
MoganaKumaran
New User
Joined: 26 Jun 2007 Posts: 20 Location: Chennai
|
|
|
|
All,
I have framed a query like this ( is it possible to use array var in WHERE clause )
select * from
tab1
where itm_typ in (:WS-ITM-TYP(1),:WS-ITM-TYP(2),:WS-ITM-TYP(3),
:WS-ITM-TYP(4),:WS-ITM-TYP(5),:WS-ITM-TYP(6),
:WS-ITM-TYP(7),:WS-ITM-TYP(8),:WS-ITM-TYP(9) ..... upto 25
We have declared an array
01 TABLES.
05 WS-ITM-TYP-CD-TABLE OCCURS 25 TIMES INDEXED BY ITM-INX
10 WS-ITM-TYP PIC S9(04) COMP VALUE ZEROS.
The value for this WS-ITM-TYP is populated from another parm table and the values are populated by using UNSTRING .At present we have 10 values and the values might be added in future, so we are using an array .
Kindly clarify if its possible to use array variables in WHERE predicate of a query.
Thanks in advance.
Regards,
mohan |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
what sql error are you receiving? |
|
Back to top |
|
|
MoganaKumaran
New User
Joined: 26 Jun 2007 Posts: 20 Location: Chennai
|
|
|
|
Hi Dick,
It gives a precompiler error exactly at the line nbr where my array var are used in WHERE clause
ILLEGAL SYMBOL "(". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: . CONCAT || / - + MI
Thanks & Regards,
Mohan |
|
Back to top |
|
|
prino
Senior Member
Joined: 07 Feb 2009 Posts: 1306 Location: Vilnius, Lithuania
|
|
|
|
Use REDEFINES? |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
from the manual:
Example 4: The following example obtains the phone number of an employee in
DSN8910.EMP where the employee number (EMPNO) is a value specified within
the COBOL structure defined below.
Code: |
77 PHNUM PIC X(6).
01 EMPNO-STRUCTURE.
05 CHAR-ELEMENT-1 PIC X(6) VALUE '000140'.
05 CHAR-ELEMENT-2 PIC X(6) VALUE '000340'.
05 CHAR-ELEMENT-3 PIC X(6) VALUE '000220'.
...
EXEC SQL DECLARE PHCURS CURSOR FOR
SELECT PHONENO FROM DSN8910.EMP
WHERE EMPNO IN
(:EMPNO-STRUCTURE.CHAR-ELEMENT-1,
:EMPNO-STRUCTURE.CHAR-ELEMENT-2,
:EMPNO-STRUCTURE.CHAR-ELEMENT-3)
END-EXEC.
EXEC SQL OPEN PHCURS
END-EXEC.
EXEC SQL FETCH PHCURS INTO :PHNUM
END-EXEC. |
the db2 precompiler is not able to resolve an index/subscripted address. |
|
Back to top |
|
|
MoganaKumaran
New User
Joined: 26 Jun 2007 Posts: 20 Location: Chennai
|
|
|
|
Dick,
The values for this array var are passed dynamically from a Application parm table.I should not harcode the values in my application program.
If any item types need to be added/changed/deleted ,no changes should be made to the program.
Thanks & Regards,
Mohan |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
oh for crying-out-loud.
I provided you with the syntax.
as prino said,
redefine the lvl 1 as a table.
do your dynamic load,
open the cursor
fetch
close.
you are welcome. |
|
Back to top |
|
|
|