USING LIKE IN WHERE CONDITION TO RETRIVE DATA FROM DATABASE

[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



Parents
  • Verified Answer

    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.

Reply
  • Verified Answer

    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.

Children
No Data