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:
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.