Skip to main content

[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



[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



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.

[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



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.


[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



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.

Terms & ConditionsAccessibility statement