100 pts.
I am trying to join our sales analysis file, all fields, with our accounts receivable file using SQL. I can extract the information okay but when I issue the following fetch command: FETCH NEXT FROM SALESSORT INTO :SALESREC ,:CUSTREC it skips the null records. I have spent the last 6 hours researching this problem with no success. Is there any way to read in the null records? Right now we are using to many work files to do this kind of project and I am trying to change that. Any help will greatly be appreciated.

Answer Wiki

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

Right, every field in SalesRec or CustRec would have to be coded as allow null, then your code might work.

Rather than a join
you could code a simple select to fetch each record from the “outer” table
and within that loop declare a cursor which selects records from the inner table based on the :cust# from the outer table, open and process records from the “inner” table

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.
  • RevDuaneSr
    Just in case you REALLY want to use the join, though, you would have to use a statement similar to IFNULL(FIELD, 0) on each of the fields in the secondary file. The example I put here is, of course, for a numeric field. It simply says that if that field is NULL, pass back the value after the comma. I think that would work, but would take a lot of effort if your file has a lot of fields in it.
    10 pointsBadges:
  • TomLiotta
    ...it skips the null records. Please describe what you mean by "null records". Do you mean records that have fields with null values? Or do you mean records that don't have a match in the other file? Tom
    125,585 pointsBadges:
  • philpl1jb
    Tom You are expecting a reply on a question that is 2 1/2 years old? But since the record could not be null, it mst be one or more fields Phil
    54,090 pointsBadges:
  • TomLiotta
    No, not expecting an answer. But somebody else might have a similar problem and think of better wording. Although a record wouldn't exactly be null, it could be that it was an expression meaning "no match" which is effectively a 'null record'. That could indicate a problem with the JOIN specification. To complicate things, English is often a second language. A poster might choose words in ways that aren't common, even when it all looks very good. Sometimes meaning has to be sought. Tom
    125,585 pointsBadges:

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.

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


Share this item with your network: