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
  • 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.

Reply
  • 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.

Children
No Data