View previous topic :: View next topic
|
Author |
Message |
Bhagyalakshmi C
New User
Joined: 24 Jul 2007 Posts: 38 Location: Bangalore
|
|
|
|
Hi all,
I am trying to fetch the records in a table into the cursor and write it into a file. The table contains few null values. when i try to fetch these null values i am not able to get those column values.
I think i hav mentioned the problem clearly. If any one cant get what i mean please post, i will reply..
Can any one help me!!!!! please!!!!!
Thank You!!!!!!!! |
|
Back to top |
|
|
Bhagyalakshmi C
New User
Joined: 24 Jul 2007 Posts: 38 Location: Bangalore
|
|
|
|
Hi,
let me also tell you the problem i what i face.
when i print the SQLCODE i get it as -305.
i guess its some error regarding the "NULL INDICATOR".
i dont know what should be done for this..
Please help me!!!!!!!
Thanks!!!!!!!! |
|
Back to top |
|
|
ksk
Active User
Joined: 08 Jun 2006 Posts: 355 Location: New York
|
|
|
|
SQL code -305 menas, "Null Indicator is Needed". You might be doing this operation on a non-null field or where Null indicator is not declared. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
take a look at the sql manual, search for null indicators, host variables. |
|
Back to top |
|
|
Bhagyalakshmi C
New User
Joined: 24 Jul 2007 Posts: 38 Location: Bangalore
|
|
|
|
Thank you!!
I tried using the null indicator also.
Code: |
PROG1:PROCEDURE OPTIONS(MAIN);
EXEC SQL INCLUDE SQLCA;
DCL INDVAR BINARY FIXED(4);
DCL 1 REPORT,
5 EMPNAME CHAR(15),
5 EMPNO CHAR(5),
5 DEPT CHAR(5);
DCL DB2OUT FILE OUTPUT RECORD ENV( FB BLKSIZE(1000) RECSIZE(25));
EXEC SQL DECLARE C1 CURSOR FOR
SELECT EMPNAME,EMPNO,DEPT FROM EMPLOYEE
WHERE EMPNO='11111';
EXEC SQL OPEN C1;
OPEN FILE (DB2OUT) TITLE('OUTFILE');
EXEC SQL FETCH C1 INTO :REPORT.EMPNAME,
:REPORT.EMPNO,
:REPORT.DEPT :INDVAR;
WRITE FILE(DB2OUT) FROM(REPORT);
EXEC SQL CLOSE C1;
CLOSE FILE(DB2OUT);
END PROG1; |
this is my program(PLI program).Here "Employee" is the table where the "dept" contain null values.
I know some changes should be made in my program.
please check and tell me what are the changes i have to do the above program.
Thank you!!!!! |
|
Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007 Posts: 632 Location: Wisconsin
|
|
|
|
You need a one byte (I think that is the size) flag. Where did you declare INDVAR? That needs to be somewhere |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
Bhagyalakshmi C,
Code: |
EXEC SQL FETCH C1 INTO :REPORT.EMPNAME,
:REPORT.EMPNO,
:REPORT.DEPT :INDVAR;
+++++++++++++++++++++++insert additional code as suggested below
WRITE FILE(DB2OUT) FROM(REPORT);
|
before you write the file, check INDVAR for a value of -1. If equal, populate REPORT.DEPT with an acceptable literal (like 'NONE') and then write the file.
I am not a PL1 type, so I can not suggest the code. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
as I can not edit my posts, I will add this addendum. I congratulate you for the Good Work that you accomplished by yourself. The null indicator variable was correctly defined and your SQL syntax is excellent.
As I indicated above, you only need to check it for a -1.- If INDVAR = -1, you have a null return for REPORT.DEPT and you need to populate REPORT.DEPT with a literal of your own choosing.
- If INDVAR <> -1, then REPORT.DEPT was populated by DB2 with a department name/number contained in the row corresponding to the employee that was returned.
again, good job! I don't mind helping people who try to help themselves. |
|
Back to top |
|
|
Bhagyalakshmi C
New User
Joined: 24 Jul 2007 Posts: 38 Location: Bangalore
|
|
|
|
Thanks Dick!!!!
Thanks a lot..
I got it!!!!!!!!!!! |
|
Back to top |
|
|
|