View previous topic :: View next topic
|
Author |
Message |
ranjani Warnings : 1 New User
Joined: 25 Oct 2006 Posts: 13
|
|
|
|
in my table, i have a null value in the NAME field in the 2nd row. When i tried to read the values in the table, using cursor, my cursor fetch returned the same NAME field value as of the first row while fetching the second row 'NAME" field. So is that host variables will not be able to read NULL values. Do we have to use indicator variable and set the column value to SPACES if the indicator value is negative???? |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Yes, if the null-indicator is set, either ignore the (previous) value for that field or move spaces (or whatever is appropriate for your situation). |
|
Back to top |
|
|
sachin_star3 Warnings : 1 New User
Joined: 30 Sep 2006 Posts: 78 Location: pune
|
|
|
|
yes you have to move some value when you get null indicator value is negative
because host variable not able to detect the null value ,means host variavles means the variables structured as for hast language i.e. cobol and cobol cannot read the null values so the null indicator comes in picture
i given you programme
check it out
Code: |
ID DIVISION
PROGRAME-ID. SACHIN.
DATA DIVISION.
EXEC SQL
INCLUDE MEMBER1
END-EXEC.
EXEC SQL
INCLUDE SQLCA
END-EXEC.
EXEC SQL
DECLARE CURSOR C1 FOR
SELECT * FROM EMP
WORKING-STORAGE SECTION
77 N1 PIC 9(4) USAGE COMP.
PROCEDURE DIVISION.
EXEC SQL
OPEN C1
END-EXEC.
PERFORM FETCH-PARA VARYING I FROPM 1 BY 1 UNTIL SQLCODE NO=100
EXEC SQL
CLOSE C1
END EXEC
STOP RUN.
[b]FETCH-PARA[/b]
EXEC SQL
FETCH INTO :ECODE,:ENAME,:ESAL:N1
END-EXEC.
IF SQLCODE=-1
MOVE "NULL" TO ESAL |
================= FROM-
SACHIN BORASE
PUNE |
|
Back to top |
|
|
MoLong
New User
Joined: 07 Feb 2007 Posts: 32 Location: Chennai
|
|
|
|
Hi Sachin,
for the above prg i dont think we need to use the variable I.
Also it is better to compare N1 < 0 rather than SQLCODE. |
|
Back to top |
|
|
sachin_star3 Warnings : 1 New User
Joined: 30 Sep 2006 Posts: 78 Location: pune
|
|
|
|
yes that can also work fine |
|
Back to top |
|
|
vini_srcna
Active User
Joined: 26 May 2005 Posts: 178 Location: Copenhagen, Denmark
|
|
|
|
IF SQLCODE=-1 MOVE "NULL" TO ESAL
I dont think you will get a SQLCODE of -1. DB2 just sets the NULL indicator. In the above example it will set NI to -1. SQLCODE would be still zero. (+100 if its end of cursor ).
When DB2 retrieves a NULL value for the query it sets the indicator to -1 and it does nothing to the host variable. So it would contain the same value.
Quote: |
So is that host variables will not be able to read NULL values. Do we have to use indicator variable |
You cannot read witout using the indicator variable. It would through negative sql code while during fetch (or select ) mostlikely -305. |
|
Back to top |
|
|
|