SQL0305 - Fetch returned a null value???

Posted by: Chris Proctor

Programmer Analyst

Columbia Sports Company

Portland, OR

SQL0305 - Fetch returned a null value???
has no ratings.

Published: 14 Jan 2013
Revised: 23 Jan 2013 - 4111 days ago
Last viewed on: 26 Apr 2024 (13112 views)

Main URL:

SQL0305 - Fetch returned a null value??? Published by: Chris Proctor on 14 Jan 2013 view comments(5)

Hi all. I'm receiving the following error in my joblog numerous times and it appears, according to the message, that the select is returning a null value to the host variable (TTLOH), right? If it is possible that no value is returned, is there a way to bypass the error? I'm not exactly sure what is meant by the "descriptor area", or if that's something that I need to create or change.

Here's the error:

CAUSE . . . . . : A FETCH, EMBEDDED SELECT, CALL, GET DESCRIPTOR, OR A SET

OR VALUES INTO STATEMENT HAS RESULTED IN A NULL VALUE, BUT AN INDICATOR

VARIABLE WAS NOT SPECIFIED FOR HOST VARIABLE TTLOH. THE RELATIVE POSITION

OF THE HOST VARIABLE IN THE INTO CLAUSE OR PARAMETER LIST IS 6. IF THE HOST

VARIABLE NAME IS *N, A DESCRIPTOR AREA WAS SPECIFIED. IF THIS ERROR OCCURS

ON A GET DESCRIPTOR STATEMENT, THE NULL VALUE IS BEING RETURNED BUT THE

INDICATOR ITEM WAS NOT SPECIFIED ON THE GET DESCRIPTOR STATEMENT. RECOVERY

. . . : SPECIFY AN INDICATOR VARIABLE, AND PRECOMPILE THE PROGRAM AGAIN.

IF THIS IS A GET DESCRIPTOR STATEMENT, SPECIFY BOTH THE DATA ITEM AND THE

INDICATOR ITEM. PRECOMPILE THE PROGRAM AGAIN.

And here's the SQL statement:

C/EXEC SQL

C+ SELECT sum(A.ISHAND) into :TTLOH FROM INSBAL A WHERE

C+ A.ISTORE = :STRNUM AND A.IDEPT = :IDEPT AND A.ISDEPT = :ISDEPT

C+ AND A.ICLAS = :ICLAS AND A.ISCLAS = :ISCLAS AND A.ISHAND > 0

C/END-EXEC

Thanks!

Chris

Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)

Posted by: bobcozzi

Site Admin ****

Chicagoland

www.cozTools.com/qryfile

Comment on: SQL0305 - Fetch returned a null value???
Posted: 11 years 3 months 11 days 7 hours 54 minutes ago

Declare a 5i0 variable and append it to the variable you're reading into.

D myindY. S 5i 0

INTO :ttloh indicator :myindy

Posted by: DaleB

Premium member *

Reading, PA

Comment on: SQL0305 - Fetch returned a null value???
Posted: 11 years 3 months 10 days 23 hours 26 minutes ago

And then test it, of course. Bob's :myindY is sort of equivalent to %NULLIND(). See Database Embedded SQL Programming, Common concepts..., Using host variables..., Indicator variables in applications that use SQL.

If the value for the result column is null, SQL puts a -1 in the indicator variable (I suggest a named constant). Other values are possible.

The word INDICATOR is optional. It's important to note that the indicator variable immediately follows the value variable with no comma; a comma moves you to the next result column. A space is not required between the host variable and the indicator variable (the colon acts as the separator), but I like include a space.

Posted by: jjcllhn

Premium member *

Comment on: SQL0305 - Fetch returned a null value???
Posted: 11 years 3 months 10 days 21 hours 19 minutes ago

Could provide a appropriate default value if you don't want to mess with null indicators:

C/EXEC SQL

C+ SELECT IFNULL(sum(A.ISHAND),0) into :TTLOH FROM INSBAL A WHERE

C+ A.ISTORE = :STRNUM AND A.IDEPT = :IDEPT AND A.ISDEPT = :ISDEPT

C+ AND A.ICLAS = :ICLAS AND A.ISCLAS = :ISCLAS AND A.ISHAND > 0

C/END-EXEC

Posted by: bobcozzi

Site Admin ****

Chicagoland

www.cozTools.com/qryfile

Comment on: SQL0305 - Fetch returned a null value???
Posted: 11 years 3 months 10 days 20 hours 57 minutes ago

Jack, I like that feature too, and have used it with so called 'date' fields (6-digit zone/packed fields that are legacy date values) to test if they're equal to zero or a true date field to test if it is in fact NULL. That along with CASE/WHEN can help a lot if you code it carefully.

Posted by: SteveCCNJ

Premium member *

Egg Harbor Twp., NJ

Comment on: SQL0305 - Fetch returned a null value???
Posted: 11 years 3 months 8 days 19 hours 25 minutes ago

And just as simple is the VALUE statement

SELECT VALUE(DatFld,current date) - if DatFld is null, default to current date

COALESCE is identical to VALUE.