Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Use of 'LIKE' in sql WHERE clause in Batch COBOL pgm

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Krishnadeva Reddy

New User


Joined: 14 Nov 2007
Posts: 36
Location: Chennai

PostPosted: Sat Oct 27, 2012 7:46 pm    Post subject: Use of 'LIKE' in sql WHERE clause in Batch COBOL pgm
Reply with quote

Hello,

I'm trying to use the below SQL statement in COBOL pgm..
SELECT FIRST_NAME FROM XYZ
WHERE FIRST_NAME LIKE %:WS-VAR%
WITH UR;

From the input file for every record I'm moving 5 bytes of input Name field to WS-VAR. For Ex: I'm moving SMITH to WS-VAR. (The input value varies for every record)
Data type of FIRST_NAME in table is CHAR(25)

The above query is not working in the batch cobol program. Can you please provide any suggestions?

I also tried moving %INPUT-FIELD% to WS-VAR and coded WHERE FIRST_NAME LIKE :WS-VAR, this did not work either.
Back to top
View user's profile Send private message

Krishnadeva Reddy

New User


Joined: 14 Nov 2007
Posts: 36
Location: Chennai

PostPosted: Sat Oct 27, 2012 9:03 pm    Post subject:
Reply with quote

The input 5 bytes can be in any position of database value.

For Ex:
Input = SMITH
Database field has values like MR SMITH VICTOR, MR SMITH, XXSMITHYY etc

I'm trying to get all the above 3 values in the output from the SQL query.

Please advice.
Back to top
View user's profile Send private message
Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 785
Location: Chennai, India

PostPosted: Sat Oct 27, 2012 9:50 pm    Post subject: Re: Use of 'LIKE' in sql WHERE clause in Batch COBOL pgm
Reply with quote

Hi Krishna,

Krishnadeva Reddy wrote:
...
The above query is not working in the batch cobol program. Can you please provide any suggestions?

I also tried moving %INPUT-FIELD% to WS-VAR and coded WHERE FIRST_NAME LIKE :WS-VAR, this did not work either.


What was the result? Did you get any error?

Sorry; this is not tested; please try.

Code:

01 ws-name.
     05 filler                pic x(01) value '%'.
     05 ws-name-part          pic x(05).
     05 filler                pic x(01) value '%'.

MOVE your input name value into ws-name-part data item.

Code:
SELECT FIRST_NAME FROM XYZ
WHERE FIRST_NAME LIKE :ws-name
WITH UR;


Please try the above and let us know the result.
Back to top
View user's profile Send private message
Krishnadeva Reddy

New User


Joined: 14 Nov 2007
Posts: 36
Location: Chennai

PostPosted: Sun Oct 28, 2012 11:30 pm    Post subject: Reply to: Use of 'LIKE' in sql WHERE clause in Batch COBOL p
Reply with quote

Thanks.

Its working now with small changes.

01 WS-NAME-TEMP.
05 FILLER PIC X(01) VALUE '%'.
05 WS-NAME-PART PIC X(05).
05 FILLER PIC X(01) VALUE '%'.
01 WS-NAME REDEFINES WS-NAME-TEMP PIC X(07).

Move input name value into ws-namepart
SELECT FIRST_NAME FROM XYZ
WHERE FIRST_NAME LIKE :ws-name
WITH UR;
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7228

PostPosted: Sun Oct 28, 2012 11:45 pm    Post subject: Reply to: Use of 'LIKE' in sql WHERE clause in Batch COBOL p
Reply with quote

Did you test it with very short names?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Mon Oct 29, 2012 12:59 am    Post subject:
Reply with quote

Quote:
Its working now with small changes.


none of your changes will pre-compile or compile.

:ws-name is an undefined host variable.
ws-namepart is undefined.

following-up on what bill said.

suggest that each time:
MOVE ALL '%' TO WS-NAME-TEMP
UNSTRING <input name value> DELIMITED BY SPACE INTO WS-NAME-PART

but somehow you have it working, so you can ignore my post.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Mon Oct 29, 2012 1:12 am    Post subject:
Reply with quote

sorry,
before you whine that WS-NAME is valid,
i see that it is.

By the way, SMITH was a very bad example for a first name.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Mon Oct 29, 2012 2:18 pm    Post subject:
Reply with quote

trailing spaces icon_rolleyes.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts How does a called pgm know if its cal... Graeme Westerman COBOL Programming 4 Tue Nov 29, 2016 9:25 pm
This topic is locked: you cannot edit posts or make replies. RANDOM Function in COBOL swapnil781 COBOL Programming 2 Tue Nov 15, 2016 6:17 pm
This topic is locked: you cannot edit posts or make replies. MIPS reduction for Batch job Virendra Shambharkar All Other Mainframe Topics 8 Mon Nov 07, 2016 4:02 pm
No new posts Regarding COBOL Stored Procedure opti... selvamsrinivasan85 DB2 4 Fri Nov 04, 2016 8:57 pm
No new posts COBOL Version 6.1 Virendra Shambharkar COBOL Programming 5 Tue Nov 01, 2016 11:24 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us