IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Issue with NULL records


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
sharad_shanu

New User


Joined: 03 Oct 2006
Posts: 40

PostPosted: Wed Nov 03, 2010 6:56 pm
Reply with quote

Hi all,

I am using a cursor like one is given below:

EXEC SQL
DECLARE CURSOR C1 FOR
SELECT COL1, COL2, COL3, COL4
FROM TAB FOR FETCH ONLY
END-EXEC.

COL1, COL2 & COL4 are defined not null whereas COL3 can havee null values. I am fetching this cursor through normal process. However I am not using null indicators or any other null handling.

While fetching I was getting -305 because of this. So I handled through hardcoding my code simuilar as +0 sqlcode. However I started facing a new issue.

Whenever COL3 has valid (non-null) values, everything is fine. However when we have COL3 as null, there is nulls in respective host-var3 which is understandable. However in such cases, host-var4 (which contains COL4 value) is also blank. I could not understand why this is affecting host variable related to COL4.

Please suggest. I hope I presented my case clearly.

Regards,
Back to top
View user's profile Send private message
Phrzby Phil

Senior Member


Joined: 31 Oct 2006
Posts: 1042
Location: Richmond, Virginia

PostPosted: Wed Nov 03, 2010 7:10 pm
Reply with quote

It's been a while, but I do not understand how host_var3 can be null - COBOL variables do not have null values.

Correct me if wrong, but isn't it your host indicator variable 3 that must have a value indicating that the corresponding host var does not have a value because its corresponding db2 column is null?

Let's make sure the question is clearly posed before analyzing the situation.

Please show the syntax where you populate the host vars.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Wed Nov 03, 2010 7:28 pm
Reply with quote

1. use null indicators.

2. during population of host variables,
when there is no null-indicator for a column,
and that column is indeed null,
population of host variables stops and -305 is returned-
therefore no population of col4. (or col3)

the manual clearly states
the population of a host variable during a null condition is not accomplished.
any value contained in the host variable is not to be considered valid.

you are making an assumption that the host variable is populated when the column is null,

and you refusal to use the null-indicators is unprofessional and you have the additional problem of col4 not being populated.

you could change the select statement to be
SELECT COL1, COL2, COL4, COL3
which may provide you a solution.
since I don't work for the same company that employs you,
i really don't care what stupidity you code into the software.

Phrzby Phil,

as far as the population of col3 with 'null',
it is obvious that the TS is a very low skilled coder
and to expect that he would use proper terminology is a waste of time.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Nov 03, 2010 9:49 pm
Reply with quote

reading the manual for functions Value(),coalesce() or nullif() is probably also expecting too much?
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Wed Nov 03, 2010 10:16 pm
Reply with quote

Anyone who uses the values resulting from a fetch when there is SQL error gets what he deserves.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Wed Nov 03, 2010 11:29 pm
Reply with quote

Hi Craig,

But gee, the query runs so much faster when it fails / has no row(s) returned. . . icon_cool.gif

d
Back to top
View user's profile Send private message
Phrzby Phil

Senior Member


Joined: 31 Oct 2006
Posts: 1042
Location: Richmond, Virginia

PostPosted: Wed Nov 03, 2010 11:33 pm
Reply with quote

Anyone seen sharad_shanu lately?

I will, however, give the benefit of the doubt as regards coalesce, which from the name does not at all say what it does. Even diligently looking down a list of function names, a beginner would never know that coalesce would be useful.

/PET PEEVE
The idiotically named intnx. Couldn't SAS have put the word "date" somewhere in this function name?
/OFF PEEVE
Back to top
View user's profile Send private message
Phrzby Phil

Senior Member


Joined: 31 Oct 2006
Posts: 1042
Location: Richmond, Virginia

PostPosted: Wed Nov 03, 2010 11:35 pm
Reply with quote

Also, it's really not officially called "null indicator" I think, as it does have values indicating a few other (granted, less likely) odd situations.
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8696
Location: Dubuque, Iowa, USA

PostPosted: Wed Nov 03, 2010 11:35 pm
Reply with quote

Dick -- the old efficiency versus effectiveness debate pops up again! 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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts SFTP Issue - destination file record ... All Other Mainframe Topics 2
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Null values are considered in Total c... DFSORT/ICETOOL 6
No new posts Join multiple records using splice DFSORT/ICETOOL 5
Search our Forums:

Back to Top