Home > IT Answers > AS/400 > When using IfNull in my SQL, records are ignored
Frydaze1
20 pts.
 When using IfNull in my SQL, records are ignored
AS/400, AS/400 Records, Data Tables, RPG, SQL
I know my data table has nulls in it. If my select statement doesn't include those fields, I get hundreds of records. If I include those fields with an IfNull, it skips all but 4 records.

Here's my testing snippet:

C/Exec sql C+ DECLARE C1 CURSOR FOR C+ SELECT C+ accountID, C+ ifNull(address,' ') C+ FROM AccountSetup C+ WHERE AccountType = 'D' C/End-Exec

C/Exec sql C+ OPEN C1 C/End-Exec C DoU SQLCOD <> 0 C/Exec sql C+ FETCH C1 C+ INTO :xaccountID,:xaddress C/End-Exec

C If SQLCOD <> 0 C Leave C EndIf

C Eval xaccountid = xaccountid

C EndDo C/Exec sql C+ CLOSE C1 C/End-Exec

 

If I go straight to STRSQL and run it, it's fine.  Which is making it very difficult for me to figure out.



Software/Hardware used:
AS400
ASKED: December 18, 2010  12:10 AM
UPDATED: December 24, 2010  3:15 AM
RELATED QUESTIONS
Answer Wiki:
Once more with better formatting... <pre> C/Exec sql C+ DECLARE C1 CURSOR FOR C+ SELECT C+ accountID, C+ ifNull(address,' ') C+ FROM AccountSetup C+ WHERE AccountType = 'D' C/End-Exec C/Exec sql C+ OPEN C1 C/End-Exec C DoU SQLCOD <> 0 C/Exec sql C+ FETCH C1 C+ INTO :xaccountID,:xaddress C/End-Exec C If SQLCOD <> 0 C Leave C EndIf C Eval xaccountid = xaccountid C EndDo C/Exec sql C+ CLOSE C1 C/End-Exec </pre>
Last Wiki Answer Submitted:  December 18, 2010  12:12 am  by  Frydaze1   20 pts.
All Answer Wiki Contributors:  Frydaze1   20 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

It might be your SQLCOD .. 100 is no data found. You will want to find out what value you are actually getting.

•If SQLCODE = 0 and SQLWARN0 is blank, execution was successful.
•If SQLCODE = 100, no data was found. For example, a FETCH statement returned no data, because the cursor was positioned after the last row of the result table.
•If SQLCODE > 0 and not = 100, execution was successful with a warning.
•If SQLCODE = 0 and SQLWARN0 = ‘W’, execution was successful with a warning.
•If SQLCODE < 0, execution was not successful.

and :x accountID,:xaddress must be correct types for accountID, and address
Phil

philpl1jb
 44,180 pts.
 

You might also want to try bringing a null indicator in when you FETCH the column: 

C/Exec sql 
C+ FETCH C1 
C+ INTO :x accountID,:xaddress :x addrind
C/End-Exec

Does it “skip” rows or does it end after retrieving four rows? That is, is the fifth row the first one with a null address?

Tom

TomLiotta
 108,135 pts.
 

check ur SQLCODE…

IF its negitive eg -504 means ur getting null values in field while fetching

Ddaswani
 0 pts.
 

If I go straight to STRSQL and run it, it’s fine.

Note that you can’t run the same SQL in STRSQL. In your embedded SQL, you are creating a CURSOR and then FETCHing into host (program) variables. This is a distinctly different process and requires different analysis.

You can test general query structures with STRSQL. But “host variable” procedures are basically guesswork as far as STRSQL is concerned.

Tom

TomLiotta
 108,135 pts.