Midrange News for the IBM i Community


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 - 1728 days ago
Last viewed on: 16 Oct 2017 (6249 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

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                                 

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: SQL0305 - Fetch returned a null value???
Posted: 4 years 9 months 2 days 2 hours 7 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: 4 years 9 months 1 days 17 hours 40 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: 4 years 9 months 1 days 15 hours 32 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
Comment on: SQL0305 - Fetch returned a null value???
Posted: 4 years 9 months 1 days 15 hours 11 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: 4 years 8 months 29 days 13 hours 38 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.