SQL Fetch

20 pts.
Tags:
Fetch
Join logical files
SQL
I an joing tow files with inner join. I want all the fields from the first file and only two fields from the other file. I defined an external DS for the first file then included that DS in another DS with the two fields from the other file but program will no compile. Will not read the last DS in the fetch statement. RLS e ds extname(rs) rd ds fmt1 likeds(rls) fmt2 30a fmt3 2a Fetch CSR into : rd does not work. Any ideas why

Answer Wiki

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

You need to use the keyword QUALIFIED on the DS specification for data structure RD :

RLS e ds extname(rs)

rd ds QUALIFIED
fmt1 likeds(rls)
fmt2 30a
fmt3 2a

Now your program will compile, BUT, I suspect that you will have problems with the SQL.

This is because the data structure is now qualified – that is, every field name in the structure is prefixed by the structure name. So, your FMT2 field is now called RD.FMT2 – and so SQL cannot match it with the names of the fields in the files it is referencing.

I’m afraid that you will have to do it the boring way, by creating a data structure (unqualified and not externally-described) with the names of all the fields explicitly stated as subfield names.

Or, you can create two externally-described data structures, one for each of the files you are reading from in your SQL, and then in the SELECT and FETCH statements you write the full explicit list of field names. This is the way I prefer to do it.

Regards,

Sloopy

Thanks you but I got it to work by just coding the two remaining fields at the end of the DS statement:

rd eds extname(rdlftm)
fmt1 30a
fmt2 2a.

From Sloopy:

Excellent! Do you know, I did not think you could do that – thanks for teaching me something new!

– Sloopy

Discuss This Question: 2  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
  • Denny Cherry
    It would be helpful if you said which programming language you are using.
    66,190 pointsBadges:
    report
  • TomLiotta
    The reason it worked is because you defined a "single field" named 'fmt1' as a structure -- a DS. SQL has no match for any columns against a DS named fmt1. Note that if you had created an external SQL VIEW to define the JOIN, you could simply have used that as the basis for the whole DS. All of the columns would automatically be included. ...which programming language... It wasn't necessary. The example source code identified the language unambiguously. Any answer would only come from someone fluent in the language. 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