Highlighted
Absent Member.
Absent Member.
933 views

USING LIKE IN WHERE CONDITION TO RETRIVE DATA FROM DATABASE

Jump to solution

[Migrated content. Thread originally posted on 01 February 2012]

FIRST STATEMENT WORKS FINE,BUT WHEN I USE VARIABLE IDENTIFIER DOES NOT WORK, I KNOW THE LENGTH OF VARIABLE IS THE REASON SO HOW CAN I FIX THIS.



EXEC SQL
DECLARE CSR17 CURSOR FOR SELECT DISTINCT
A.EMPL_NO
,A.ENGL_NAME
,A.ARABIC_NAME
FROM PERSONAL A
WHERE ( A.ENGL_NAME LIKE N'%JAMILA%' )
END-EXEC
EXEC SQL OPEN CSR17 END-EXEC
PERFORM UNTIL SQLCODE
EXEC SQL
FETCH CSR17 INTO
:PERSONAL-EMPL-NO
,:PERSONAL-ENGL-NAME:PERSONAL-ENGL-NAME-NULL
,:PERSONAL-ARABIC-NAME:PERSONAL-ARABIC-NAME-NULL
END-EXEC
IF SQLCODE = 0
PERFORM VARYING IDX FROM 1 BY 1 UNTIL IDX > SQLERRD(3)

DISPLAY PERSONAL-ENGL-NAME

END-PERFORM
END-IF
END-PERFORM
EXEC SQL CLOSE CSR17 END-EXEC




MOVE "%JAMILA% " TO WR-NAME


PERFORM GET-LENGTH
EXEC SQL
DECLARE CSR18 CURSOR FOR SELECT DISTINCT
A.EMPL_NO
,A.ENGL_NAME
,A.ARABIC_NAME
FROM PERSONAL A
WHERE ( A.ENGL_NAME LIKE : WR-NAME )
END-EXEC
EXEC SQL OPEN CSR18 END-EXEC
PERFORM UNTIL SQLCODE
EXEC SQL
FETCH CSR18 INTO
:PERSONAL-EMPL-NO
,:PERSONAL-ENGL-NAME:PERSONAL-ENGL-NAME-NULL
,:PERSONAL-ARABIC-NAME:PERSONAL-ARABIC-NAME-NULL
END-EXEC
*> Process data from FETCH
IF SQLCODE = 0
PERFORM VARYING IDX FROM 1 BY 1 UNTIL IDX > SQLERRD(3)

DISPLAY PERSONAL-ENGL-NAME

END-PERFORM
END-IF
END-PERFORM
EXEC SQL CLOSE CSR18 END-EXEC



0 Likes
1 Solution

Accepted Solutions
Highlighted
Micro Focus Expert
Micro Focus Expert

RE: USING LIKE IN WHERE CONDITION TO RETRIVE DATA FROM DATABASE

Jump to solution
The problem is that the space padding in the data item causes the comparison to be false if the actual column data does not also have those spaces.

You can get around this by padding the data item with "%" characters as follows:

MOVE ALL "%" to WR-NAME
MOVE "%JAMILA% " TO WR-NAME(1:8)

You could also define the host variable as a group item using level 49s as follows:

something like:

01 WR-NAME.
   49  WR-NAME-LEN     PIC S9(4) COMP.
   49  WR-NAME-VALUE   PIC N(50) USAGE NATIONAL.

MOVE "%JAMILA%" TO WR-NAME-VALUE
MOVE 8 TO WR-NAME-LEN

Then use group name WR-NAME in WHERE clause.
The precompiler recognizes the 49 levels as a variable length host variable and will limit the size of the field to the value of the length field.

View solution in original post

0 Likes
3 Replies
Highlighted
Micro Focus Expert
Micro Focus Expert

RE: USING LIKE IN WHERE CONDITION TO RETRIVE DATA FROM DATABASE

Jump to solution
The problem is that the space padding in the data item causes the comparison to be false if the actual column data does not also have those spaces.

You can get around this by padding the data item with "%" characters as follows:

MOVE ALL "%" to WR-NAME
MOVE "%JAMILA% " TO WR-NAME(1:8)

You could also define the host variable as a group item using level 49s as follows:

something like:

01 WR-NAME.
   49  WR-NAME-LEN     PIC S9(4) COMP.
   49  WR-NAME-VALUE   PIC N(50) USAGE NATIONAL.

MOVE "%JAMILA%" TO WR-NAME-VALUE
MOVE 8 TO WR-NAME-LEN

Then use group name WR-NAME in WHERE clause.
The precompiler recognizes the 49 levels as a variable length host variable and will limit the size of the field to the value of the length field.

View solution in original post

0 Likes
Highlighted
Absent Member.
Absent Member.

RE: USING LIKE IN WHERE CONDITION TO RETRIVE DATA FROM DATABASE

Jump to solution
thanks
I did it,
MOVE ALL "%" to WR-NAME
MOVE "%JAMILA% " TO WR-NAME(1:8) works fine




but 01 WR-NAME.
49 WR-NAME-LEN PIC S9(4) COMP.
49 WR-NAME-VALUE PIC N(50) USAGE NATIONAL.

MOVE "%JAMILA%" TO WR-NAME-VALUE
MOVE 8 TO WR-NAME-LEN

it does not work. any way the problem solved. thanks again.


0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

RE: USING LIKE IN WHERE CONDITION TO RETRIVE DATA FROM DATABASE

Jump to solution
The 49 level should work.
I tested this here with Net Express 5.1 WS6 and I get the correct row back when I use this.

What product and version number are you using?
Do you get an error when using the level 49s or does it just not return the expect result?

The code I provided was just a sample.
You should use whatever your host variable was defined as.
In the example the host variable was PIC N(50)

01 WR-NAME.
49 WR-NAME-LEN PIC S9(4) COMP.
49 WR-NAME-VALUE PIC N(50) USAGE NATIONAL.

You could also try using a national literal:

MOVE N'%JAMILA%' TO WR-NAME-VALUE
MOVE 8 TO WR-NAME-LEN

although this worked either way on my system.

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.