I was recently asked the following question, via Facebook:
How we can read a particular member of physical file thru SQL EXEC in RPGLESQL program ?
Others suggested using "Override with Data Base File" (OVRDBF) command, which could be used. But there is a way to do this just in SQL.
In this example I have a file, TESTFILE, which has two members that contain a different number of records:
| Member | Records |
| First | 3 |
| Second | 2 |
I need to determine the number of records/columns there are in member SECOND.
The way to access different members in a multiple member file using SQL is to use ALIAS. An ALIAS is a temporary rename of a table. I always create ALIASes in QTEMP, as it cannot interfere with other jobs. With a multimember scenario like this I always name the member
As the person posing the asked how to do this in a SQLRPGLE creating the alias would look like this:
02 exec sql CREATE ALIAS QTEMP/TESTFILE_SECOND
FOR TESTFILE(SECOND) ;
|
Or I could use a variable name for the name of the member:
Member = 'SECOND' ;
Text = 'CREATE ALIAS QTEMP/TESTFILE_' + %trimr(Member) +
' FOR TESTFILE(' + %trimr(Member) + ')' ;
exec sql EXECUTE IMMEDIATE :Text ;
|
When you have finished using the ALIAS you have delete it. That is done using the DROP ALIAS, see below:
04 exec sql DROP ALIAS QTEMP/TESTFILE_SECOND ; |
To perform the count of the number of records/rows in my ALIAS I am going to use a SELECT:
SELECT COUNT(*) INTO :Count FROM QTEMP/TESTFILE_SECOND |
I can put this all together like this:
01 dcl-s Count int(10) ;
02 exec sql CREATE ALIAS QTEMP/TESTFILE_SECOND
FOR TESTFILE(SECOND) ;
03 exec sql SELECT COUNT(*) INTO :Count
FROM QTEMP/TESTFILE_SECOND ;
04 exec sql DROP ALIAS QTEMP/TESTFILE_SECOND ;
05 dsply Count ;
06 *inlr = *on ;
|
On line 2 I create my ALIAS.
I execute my SELECT statement to put the count of the records into the variable Count on line 3.
As I am done with the ALIAS I use the SQL DROP statement to delete it, line 4.
And finally I display the count of records in the member using the RPG DSPLY operation.
If you do not have the RPG all free PTFs then the equivalent definition specification would be:
01 D Count S 10I 0 |
This is a very simple example of how to use the ALIAS to access a member in a multi member file. I am sure you can find more useful applications for this.
You can learn more about these from the IBM web site:
This article was written for IBM i 7.1.
I like the approach...now how about wanting to process records from *ALL members in SQL? Obviously one could extract a list of members and loop through them using either OVRDBF or your ALIAS approach, it's a shame neither work with *ALL. Not that I have a particular application needing that option, it's just your thought provoking solution that's got me thinking...Mike
ReplyDeleteIt may hit the performance... If you create alias in qtemp every time. Better create a permanent alias object or ovrdbf would be good option.
ReplyDeleteThis is okay if the file has few members. I have a practical case, particular file consists of more than 1000 members.
ReplyDelete1,000 members! That is ridiculous. Really bad database design.
DeleteSQL was never designed to work with multi member files, alaises allow us to do so but they are not as neat as native I/O methods when handling them.
I had a need to read hundreds of members within SQL. I was writing a script to scan all source code in a library for a character string. I used the ALIAS option to build a list of all members and loop through them.
Delete