Using SQL to select from file members

If you have a multi-member file on the AS400, and need to look at member C instead of the first default member, here is the instructions:

First, we need to create an alias for the member.  For this, we run this SQL command:

CREATE ALIAS ALIAS1 FOR LIB1/FILE1(C)

Now, the SQL is looking in member C of file FILE1, in library LIB1.

Next, we can simply perform any SQL command over the alias ALIAS1:

SELECT * FROM ALIAS1

This would list all records from member C on the screen.

Remember to drop the alias when you are finished.  Otherwise, the next person might try to create an alias called ALIAS1, but it would already exist.

DROP ALIAS ALIAS1

Have fun!

RPGIII to RPGIV to RPG free format

I've pretty much been working with the AS400 since it first came out.  My programming career started with RPGIII.  It's a fine language, easy to write, and for the most part it does the job you need it to.  However, when RPGIV was introduced it became a much more versatile language.  File & field names could be longer, functions could be used, and many things became easier. 

Since I am "old school", I gave some resistance to learning the new language.  Once I learned RPGLE, I could hardly imagine having to go back to RPGIII.  RPG free actually became ever more versatile, and it's the language I prefer to use.

Just my opinion, but RPGIV is by far the better option to RPGIII.

Creating an SQL view

An SQL view is similar to a logical file, and are major time savers.  When performing SQL statements, you can create a view of the query you want, and then pull it up any time without having to remember what the query was to get the information. Here is a quick example of an SQL view:

First, we will create an "SQL view" called view1

CREATE VIEW VIEW1 AS
SELECT * FROM FILE1 WHERE FIELD1="AAA" AND FIELD2 < 3

Now, an SQL view called VIEW1 is created.  Until the view is dropped with the DROP VIEW VIEW1 command, this query can be run at any time.  You can sign off your AS400 session, go to the store, come back and sign on, and run the following command through SQL:

SELECT * FROM VIEW1.  This will give you the query from the above SELECT statement without having to remember it.  Pretty cool stuff in my opinion!  It's very useful for me, just because I have some really complicated queries that are difficult to remember at times.

SQL select statement from specific member of AS400 file

In some cases, you might want to select data from a file that contains multiple members, but only the data from one of the members.  Here is the instructions on how to do so.

First, we need to create an alias to define the member to the query.

CREATE ALIAS ALIAS1 FOR FILE1(MEMBER1)

Now that the alias has been created, it is pointing to that member of the file.  We can select from it as normal.

SELECT * FROM ALIAS1 WHERE FIELD1='ABC123'

This select statement selects the records from member MEMBER1 in file FILE1 when the FIELD1 field contains the value "ABC123".

Don't forget to run the following statement when you are finished.  Otherwise, someone else could accidentally use your alias from another session!

DROP ALIAS ALIAS1

AS400 SQL Update statement

If you need to update a file on the AS400 using STRSQL, the statement is fairly straightforward.  Here's a few examples of how to do this:

UPDATE LIB1/FILE1 SET FIELD1='AAA'

This statement will update the file called FILE1 in library LIB1 to have the value of AAA in field1.  This will update every record in the file to contain that value. 

UPDATE FILE1 SET FIELD1='AAA'

This is the same as the statement above, except that it does not specify the library name.  Using the SQL this way, means that it will file the first instance of FILE1 in your library list and update that file.

UPDATE FILE1 SET FIELD1='AAA' WHERE FIELD2='BBB'

This statement will update all records in file name FILE1 to have the value of AAA in FIELD1 when the FIELD2 value is equal to BBB in the record.

UPDATE FILE1 SET FIELD1=123 WHERE FIELD2>123

This statement will update all records in file name FILE1 to have the value of 123 when FIELD2 is greater than 123.  In this case, both of these fields are defined as numeric in the file, so there are no single quotes around the data.

Retrieving the relative record number in AS400 SQL

Sometimes, it can be useful to select all records from a physical file on the AS400, and to know what relative record number each one is.  For example, this came up for me because I needed to be able to use DSPPFM and view the raw data, because one of the fields was in hexidecimal format and I needed to display it in hex mode to view it easier.  This is how you do it:

SELECT RRN(A), A.*      
FROM FILE1 A

The above statements selects the relative record number of each record, and then selects all fields from the file.  This is by using the alias "A".  The 2nd line declares A as the alias for FILE1, so that the first instruction knows what file it is supposed to query.        

STRSQL AS400 "LIKE" statement

Hi all,

Here's a quick example of how to use the LIKE statement in AS400 SQL.  You can select from a file where a certain fields contain a wild card of a certain value.  For example:

SELECT * FROM FILE1 WHERE FIELD1 LIKE '%ABC%'

The above statement will select all records where FIELD1 contains the value "ABC" anywhere within the field data.  (i.e. "123ABC123", "ABC123", "123ABC")

Each of these records will appear in the query.

Enjoy!