Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

problem in fetching null values

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Bhagyalakshmi C

New User


Joined: 24 Jul 2007
Posts: 38
Location: Bangalore

PostPosted: Thu Aug 16, 2007 1:27 pm    Post subject: problem in fetching null values
Reply with quote

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
View user's profile Send private message

Bhagyalakshmi C

New User


Joined: 24 Jul 2007
Posts: 38
Location: Bangalore

PostPosted: Thu Aug 16, 2007 3:09 pm    Post subject:
Reply with quote

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
View user's profile Send private message
ksk

Active User


Joined: 08 Jun 2006
Posts: 356
Location: New York

PostPosted: Thu Aug 16, 2007 3:42 pm    Post subject:
Reply with quote

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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Thu Aug 16, 2007 3:50 pm    Post subject:
Reply with quote

take a look at the sql manual, search for null indicators, host variables.
Back to top
View user's profile Send private message
Bhagyalakshmi C

New User


Joined: 24 Jul 2007
Posts: 38
Location: Bangalore

PostPosted: Thu Aug 16, 2007 4:35 pm    Post subject:
Reply with quote

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
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Thu Aug 16, 2007 4:48 pm    Post subject:
Reply with quote

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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Thu Aug 16, 2007 5:00 pm    Post subject:
Reply with quote

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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Thu Aug 16, 2007 5:08 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Bhagyalakshmi C

New User


Joined: 24 Jul 2007
Posts: 38
Location: Bangalore

PostPosted: Thu Aug 16, 2007 6:02 pm    Post subject:
Reply with quote

Thanks Dick!!!!

Thanks a lot.. icon_biggrin.gif icon_smile.gif

I got it!!!!!!!!!!! icon_biggrin.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Fileaid Update Replace with null string descann Compuware & Other Tools 4 Mon May 15, 2017 3:00 pm
No new posts Replace values in the input data Vikas Maharnawar DFSORT/ICETOOL 10 Thu May 11, 2017 2:18 pm
No new posts Compare two files and subtract values ameetmund DFSORT/ICETOOL 7 Fri Mar 31, 2017 3:35 pm
No new posts Strings with double quotes having pro... raja Arumugam All Other Mainframe Topics 11 Thu Mar 30, 2017 10:34 am
No new posts Check Binary Values for symbols gandikk CA Products 3 Tue Mar 21, 2017 5:11 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us