is it possible to pass occurs elements in select cursor?

i_suman · New User Joined: 01 Mar 2005 Posts: 20

Hi,
I have a requirement to pass table elements(occurs elements) to SELECT query (through CURSOR) in cobol-db2 program like below

SELECT col1
col2
FROM table1
WHERE col3 = :col3-table1-var
AND col4_da >= :ws-beg-da
AND col4_da < :ws-end-da
AND col5 IN (:ws-itemtype(1),
:ws-itemtype(2),
...etc.
:ws-itemtype(10))

Does Passing occurs elements to a embedded query will work? If not,
could you please suggest me the alternative solution to achieve the same requiremnt?

Thanks alot for all ur support

dick scherrer · Posted: Mon Oct 27, 2008 1:25 am

Hello,

Did you try this?

If so, what happened?

If not, try and post back if you have any questions.

For questions like this, you will be far more productive running a simple test - someone will be here if there are questions or problems.

i_suman · New User Joined: 01 Mar 2005 Posts: 20

Hi,

I am getting the following compilation error when tried as below.

IGYDS0226-E DSNH104I DSNHPARS ILLEGAL SYMBOL "(". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: + -

DECLARE FS_RVW_10174_CSR CURSOR
WITH HOLD FOR
SELECT CRE_TS
,REVW_ITEM_TY_CD
FROM AAA_FS_RVW_10174
WHERE IR_NO = 0
AND CRE_DA >= '2003-11-14'
AND CRE_DA < '2008-12-31'
AND REVW_ITEM_TYPE_CD IN
(:WS-REVW-ITEM-TY-CD(1),
:WS-REVW-ITEM-TY-CD(2),
:WS-REVW-ITEM-TY-CD(3),
:WS-REVW-ITEM-TY-CD(4),
:WS-REVW-ITEM-TY-CD(5),
:WS-REVW-ITEM-TY-CD(6),
:WS-REVW-ITEM-TY-CD(7),
:WS-REVW-ITEM-TY-CD(8),
:WS-REVW-ITEM-TY-CD(9),
:WS-REVW-ITEM-TY-CD(10))

FOR READ ONLY
END-EXEC.

As per my understanding I think we can't pass index / subscript variable to the query in DECLARE CURSOR clause. [I tried even giving variable name instead of numbers in ws-revw-item-ty-cd( ).]

So I went with some alternative way. If some one got the solution to this please let us know.

Thanks.

ashimer · Posted: Tue Nov 04, 2008 10:35 pm

01 FILLER REDEFINES WS-SEARCH.
05 WS-REVW-ITEM-TY-CD OCCURS 100 TIMES.
10 WS-REV PIC X(10)

01 WS-SEARCH.
05 WS-SEARCH1 PIC X(10).
05 WS-SEARCH2 PIC X(10).
.......
......

USE WS-SEARCH1,2,3,4 IN YOUR IN CLAUSE ...