View previous topic :: View next topic
|
Author |
Message |
sharad_shanu
New User
Joined: 03 Oct 2006 Posts: 40
|
|
|
|
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 |
|
|
Phrzby Phil
Senior Member
Joined: 31 Oct 2006 Posts: 1042 Location: Richmond, Virginia
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
reading the manual for functions Value(),coalesce() or nullif() is probably also expecting too much? |
|
Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA
|
|
|
|
Anyone who uses the values resulting from a fetch when there is SQL error gets what he deserves. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hi Craig,
But gee, the query runs so much faster when it fails / has no row(s) returned. . .
d |
|
Back to top |
|
|
Phrzby Phil
Senior Member
Joined: 31 Oct 2006 Posts: 1042 Location: Richmond, Virginia
|
|
|
|
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 |
|
|
Phrzby Phil
Senior Member
Joined: 31 Oct 2006 Posts: 1042 Location: Richmond, Virginia
|
|
|
|
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 |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008 Posts: 8696 Location: Dubuque, Iowa, USA
|
|
|
|
Dick -- the old efficiency versus effectiveness debate pops up again! |
|
Back to top |
|
|
|