COBOL/DB2 query - Variable length working storage variable

[Gaurav Chauhan]

I have a Cobol/DB2 query:

EXEC SQL
Select A.CUDT_ID
INTO :WS-CUST-ID
FROM CUST A
WHERE A.CUST_NM = :WS-CUST-NAME
WITH UR
END-EXEC.

Working storage variable is defined as:
05 WS-CUST-NAME PIC X(20).

In this query I don't want to use all 20 bytes of working storage variable for comparison.
Number of bytes to be used will be found at run time.
WS-COUNT variable will hold the no. of bytes for comparison.

I am trying to use this:

EXEC SQL
Select A.CUDT_ID
INTO :WS-CUST-ID
FROM CUST A
WHERE A.CUST_NM = :WS-CUST-NAME(1:WS-COUNT )
WITH UR
END-EXEC.

This is not working.....Is there any other workaround??

[CICS Guy]

There is a DB2 thing called SUBSTR that allows sub-strings of fields to be used......

[Gaurav Chauhan]

Yes but SUBSTR can be used only with DB2 table columns....
I want use sub-string of a working storage variable..

[CICS Guy]

If you say so.....
If reference modification won't work.....
Redefine (or move to) an occurs depending on structure and compare against it....
How long is the DB column, 20 characters too?

[Gaurav Chauhan]

Yes DB2 column for name is 20 bytes...But only at run time I wiil decide how many bytes of working storage to be compared against DB2 column..
Query could be something like this:

EXEC SQL
Select A.CUDT_ID
INTO :WS-CUST-ID
FROM CUST A
WHERE SUBSTR(A.CUST_NM1,:WS10-SCR-CNT) = :WS-CUST-NAME(1:WS-COUNT )
WITH UR
END-EXEC.

SUBSTR on L.H.S. for CUST_NM column is working....
But syntax for host variable in embedded query is not working..

[CICS Guy]

SUBSTR on L.H.S. for CUST_NM column is working....
But syntax for host variable in embedded query is not working..

Good, at least you have half of it....
Redefine (or move to) an occurs depending on structure and compare against it....
05 WS-CUST-NAME PIC X(20).
05 WS-CUST-NAME-V REDEFINES
WS-CUST-NAME.
10 FILLER PIC X OCCURS 20 TIMES DEPENDING ON WS-COUNT PIC X.

WHERE SUBSTR(A.CUST_NM1,:WS10-SCR-CNT) = :WS-CUST-NAME-V)

[Gaurav Chauhan]

I have solved it like this:

I declared another name variable
05 WS-CUST-NAME-HOLD PIC X(20) Value '%%%%%%%%%%%%%%%%%%%%'.

Then
Move WS-CUST-NAME(1:WS-COUNT ) TO WS-CUST-NAME-HOLD.

Finally the query

EXEC SQL
Select A.CUDT_ID
INTO :WS-CUST-ID
FROM CUST A
WHERE SUBSTR(A.CUST_NM,:WS10-SCR-CNT) LIKE :WS-CUST-NAME-HOLD
WITH UR
END-EXEC.

n its working :)

[waleeed]

Yes DB2 column for name is 20 bytes...But only at run time I wiil decide how many bytes of working storage to be compared against DB2 column..

[NicC]

Waleeed - welcome to the forum but please do not resurrect old posts (this one is over 8 years old).

[enrico-sorichetti]

The gentleman is a troll that replied to post a link to some online gaming site

topic locked