|
View previous topic :: View next topic
|
| Author |
Message |
Krishnadeva Reddy
New User
Joined: 14 Nov 2007
Posts: 37
Location: Chennai
|
|
|
|
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 |
|
 |
Krishnadeva Reddy
New User
Joined: 14 Nov 2007
Posts: 37
Location: Chennai
|
|
|
|
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 |
|
|
Gnanas N
Active Member
Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India
|
|
|
|
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 |
|
|
Krishnadeva Reddy
New User
Joined: 14 Nov 2007
Posts: 37
Location: Chennai
|
|
|
|
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 |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| Did you test it with very short names? |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| 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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
trailing spaces  |
|
| Back to top |
|
|
|
|
