COBOL/DB2 query - Variable length working storage variable



Support for OS/VS COBOL, VS COBOL II, COBOL for OS/390 & VM and Enterprise COBOL for z/OS

COBOL/DB2 query - Variable length working storage variable

Postby Gaurav Chauhan » Mon Nov 05, 2007 2:05 pm

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??
Gaurav Chauhan
 
Posts: 12
Joined: Mon Nov 05, 2007 1:50 pm
Has thanked: 0 time
Been thanked: 0 time

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

Postby CICS Guy » Mon Nov 05, 2007 3:55 pm

There is a DB2 thing called SUBSTR that allows sub-strings of fields to be used......
CICS Guy
 
Posts: 246
Joined: Wed Jun 20, 2007 4:08 am
Has thanked: 0 time
Been thanked: 0 time

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

Postby Gaurav Chauhan » Mon Nov 05, 2007 4:12 pm

Yes but SUBSTR can be used only with DB2 table columns....
I want use sub-string of a working storage variable..
Gaurav Chauhan
 
Posts: 12
Joined: Mon Nov 05, 2007 1:50 pm
Has thanked: 0 time
Been thanked: 0 time

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

Postby CICS Guy » Mon Nov 05, 2007 7:57 pm

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?
CICS Guy
 
Posts: 246
Joined: Wed Jun 20, 2007 4:08 am
Has thanked: 0 time
Been thanked: 0 time

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

Postby Gaurav Chauhan » Tue Nov 06, 2007 9:04 am

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..
Gaurav Chauhan
 
Posts: 12
Joined: Mon Nov 05, 2007 1:50 pm
Has thanked: 0 time
Been thanked: 0 time

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

Postby CICS Guy » Tue Nov 06, 2007 3:43 pm

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)
CICS Guy
 
Posts: 246
Joined: Wed Jun 20, 2007 4:08 am
Has thanked: 0 time
Been thanked: 0 time

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

Postby Gaurav Chauhan » Tue Nov 06, 2007 6:49 pm

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 :):)
Gaurav Chauhan
 
Posts: 12
Joined: Mon Nov 05, 2007 1:50 pm
Has thanked: 0 time
Been thanked: 0 time

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

Postby waleeed » Mon Feb 08, 2016 11:45 am

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..
waleeed
 
Posts: 1
Joined: Mon Feb 08, 2016 11:41 am
Has thanked: 0 time
Been thanked: 0 time

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

Postby NicC » Mon Feb 08, 2016 4:17 pm

Waleeed - welcome to the forum but please do not resurrect old posts (this one is over 8 years old).
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times

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

Postby enrico-sorichetti » Fri Feb 12, 2016 4:36 pm

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

topic locked
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 2994
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 164 times


Return to IBM Cobol

 


  • Related topics
    Replies
    Views
    Last post