Page 1 of 1

COBOL/DB2 query - Variable length working storage variable

PostPosted: Mon Nov 05, 2007 2:05 pm
by 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??

Re: COBOL/DB2 query - Variable length working storage variable

PostPosted: Mon Nov 05, 2007 3:55 pm
by CICS Guy
There is a DB2 thing called SUBSTR that allows sub-strings of fields to be used......

Re: COBOL/DB2 query - Variable length working storage variable

PostPosted: Mon Nov 05, 2007 4:12 pm
by Gaurav Chauhan
Yes but SUBSTR can be used only with DB2 table columns....
I want use sub-string of a working storage variable..

Re: COBOL/DB2 query - Variable length working storage variable

PostPosted: Mon Nov 05, 2007 7:57 pm
by 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?

Re: COBOL/DB2 query - Variable length working storage variable

PostPosted: Tue Nov 06, 2007 9:04 am
by 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..

Re: COBOL/DB2 query - Variable length working storage variable

PostPosted: Tue Nov 06, 2007 3:43 pm
by CICS Guy
Gaurav Chauhan wrote: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)

Re: COBOL/DB2 query - Variable length working storage variable

PostPosted: Tue Nov 06, 2007 6:49 pm
by 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 :):)

Re: COBOL/DB2 query - Variable length working storage variab

PostPosted: Mon Feb 08, 2016 11:45 am
by 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..

Re: COBOL/DB2 query - Variable length working storage variab

PostPosted: Mon Feb 08, 2016 4:17 pm
by NicC
Waleeed - welcome to the forum but please do not resurrect old posts (this one is over 8 years old).

Re: COBOL/DB2 query - Variable length working storage variab

PostPosted: Fri Feb 12, 2016 4:36 pm
by enrico-sorichetti
The gentleman is a troll that replied to post a link to some online gaming site

topic locked