Specify Member in SQL Statement on AS/400 1

[vbMax]

How can a file member by specified in an SQL statement on the AS/400? If I issue the following SQL statement:
SELECT * FROM TEST/QRPGLESRC
the "first" member in the file is read. However, I would like to read a different member. I have tried the following statements:
SELECT * FROM TEST/QRPGLESRC.ABC
SELECT * FROM TEST/QRPGLESRC(ABC)
the syntax is not correct.

Thanks in advance!
- vbMax

 

[lkmalee]

Quoting the IBM SQL reference book ...Think of an alias as an alternative name for a table, view, or member of a database file. Aliases help you avoid using file overrides.... This implies to me that you should either issue on override to the file.member i.e. OVRDBF FILE(QRPGLESRC) TOFILE(TEST/QRPGLESRC) MBR(ABC) or create an alias for the member. I tried the override and it works. I did not try to create an alias. The SQL reference can be found at

http://publib.boulder.ibm.com/pubs/html/as400/v4r5/ic2924/info/db2/rbafzmstch2alias.htm#HDRCH2ALIAS

note that this is information for V4R5. I would try the override if the programming is CL or other non-SQL language. If the programming is being done in SQL, then I would try the CREATE ALIAS sql statement. See this link for usage

http://publib.boulder.ibm.com/iseri...b2/rbafymstcreatingalias.htm#HDRCREATINGALIAS

 

[os400]

Here's another slant on it, from Search400.com:

Using files with multiple members in SQL or ODBC
Jose Luis Gareli
10 Sep 2004
Rating: -3.00- (out of 5)

I've come across problems when working in SQL and ODBC with specific members in files with multiple members.
This is a simple solution that I came up with -- without using the OVRDBF or CREATE ALIAS.

Create an alias for the member of the file with multiple members:

CRTDDMF FILE(library_name/ddm_file_name) RMTFILE(*NONSTD
'library_name/file_name(member_name)') RMTLOCNAME(*RDB)
RDB(local_rdb_name)

library_name/ddm_file_name = alias for file member to use in file with
multiple members
library_name/file_name(member_name) = file member in file with multiple
members
local_rdb_name = WRKRDBDIRE, see Relational Database Name (Remote
Location = *Local)


In SQL
------

SELECT field1, field2, field3, field4
FROM library_name/ddm_file_name
WHERE field1 = 'ABCDF'


In ODBC
-------

In the screen Select Tables, to select the file:
library_name/ddm_file_name
Then, it will visualize the fields of the file (they are really the
fields of the file: library_name/file_name(member_name)), to select the
fields and to usually continue
==================================

Have not tried these myself, but worth a shot...........

 

[vbMax]

Both methods worked for me - the OVRDBF and CREATE ALIAS methods. Also, I discovered that the CREATE ALIAS command in SQL is the equivalent of the OS/400 command CRTDDMF.

Thank!
vbMax