When using IfNull in my SQL, records are ignored

20 pts.
Tags:
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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>

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • philpl1jb
    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 :xaccountID,:xaddress must be correct types for accountID, and address Phil
    49,435 pointsBadges:
    report
  • TomLiotta
    You might also want to try bringing a null indicator in when you FETCH the column:
    C/Exec sql 
    C+ FETCH C1 
    C+ INTO :xaccountID,:xaddress :xaddrind
    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
    125,585 pointsBadges:
    report
  • Frydaze1
    check ur SQLCODE... IF its negitive eg -504 means ur getting null values in field while fetching
    0 pointsBadges:
    report
  • TomLiotta
    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
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following