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 - 2119 days ago
Last viewed on: 12 Nov 2018 (7823 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)

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: 5 years 9 months 28 days 22 hours 40 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: 5 years 9 months 28 days 14 hours 13 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: 5 years 9 months 28 days 12 hours 5 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: 5 years 9 months 28 days 11 hours 44 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: 5 years 9 months 26 days 10 hours 11 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.