RE: Can SQL "simulate" a multiple format logical file with a join? -- MIDRANGE-L

A couple possibilities--

1. Embedded SQL in RPG with host variables for District, School, and Student in a WHERE clause. The EXEC SQL would be the statement we've described - a UNION SELECT with ORDER BY. Then FETCH gets you each record, instead of CHAIN and READE, as you now have it. You probably want EVI indexes on these 3 columns, to aid in record selection - if you don't have such a logical already. And that'd be on all the PFILEs. I don't think the join logical will be used, anyone know?

2. Put the statement into a QMQRY (Query Management query). These can have substitution variables passed to them in the STRQMQRY command, which is used to run them. You can have the result saved to a file. Then read the file in RPG (user-open, probably).

#1 has a performance advantage - optimization is done at program creation. Be sure to have indexes to support it.

#2 is a kind of double-hitch method - similar to OPNQRYF, I suppose, but OPNQRYF could never do what we want - nor needed to.

I believe you can read views in RPG but you have no key, so you cannot position by key (SETLL) as you are doing. #1 accomplishes the same thing, basically. BTW, a view is basically an ad hoc SELECT statement - it is run every time it is used, with new optimization stage, etc. There's probably some system caching of statements these days, but embedded still has the advantage here.

HTH
Vern

At 12:55 PM 4/30/2004, you wrote:

Gentlemen,

Thank you for your responses.

What I intended to imply in the original post, was that the subfile lists
invoice and payment records for a given student, so I set lower limits on
District, School, and Student, and read-equal by this partial key to get
all the records for the student.  So, yes, there won't be many (relatively
speaking) records for a given student, but there will be a very large
number of records in the files.

I think I see what happens with UNION, but I will read up on that and
experiment with that before bothering with more questions.

One question I do have concerns the permanancy of this view / index.
Would I create this once and use it much like I would a logical file?  Or
would I have to do the SELECT everytime a different student is selected?
My entire experience with SQL is SELECT, UPDATE, & DELETE.

I presume that, by no mention of it in your replies, adding a join of
another file to both FT310 & FT320, and using one of its fields to
sequence records is not a problem?

GA


__________________________________
Do you Yahoo!?
Win a $20,000 Career Makeover at Yahoo! HotJobs
http://hotjobs.sweepstakes.yahoo.com/careermakeover
_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.