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!

Reading and processing AS400 file using imbedded SQLRPGLE

Here's a brief example of how to select records in SQLRPGLE from an AS400 file, read through them, and perform processing on them.  It is a very powerful way of manipulating data once you get started with it.

So, the source code is below.  I put comments around the source code, in my attempt to explain how the code works.  If you have any questions, comment or drop me a line!

ffile1     uf a e           k disk                                   

 *  The file above  matches the file below in layout.  In this example we are copying from

 *  file2 to file1 based on the SQL query below                                                                

d FileDS        e ds                  extname(file2)              

 * file2 is the input file that we will be reading & processing through SQL                      

c/exec sql    

 * In SQLRPGLE, at the beginning of every SQL statement, the exec statement 

 * must be performed

c+  declare C1 cursor for                                   

c+  select * from file1 where                            

c+  field1 = 'AAA' and field2 = 'BBB'   

c/end-exec           

 * In the above SQL, we are declaring a cursor called C1.  We are selecting from file1 

 * where field1 & field 2 equals the specified values.                                             

c/exec sql 
c+ open C1                                                        

c/end-exec

* The above SQL command opens the cursor and gets it ready for use.                                                         

                                                                   

c/exec sql                                                         

c+  fetch C1 into :FileDS                                          

c/end-exec                                                         

* The above SQL command reads the first record of the series into the FileDS data structure

* defined at the top of the program.  This is based on file2, which has the same fields

* as file1.  Basically it is an empty copy of file1.

                                                                   

*  if sqlcod = 0, it means that we have a record to process.  Once sqlcod is not equal to 0, 

*  it means that the end of record set has been reached, and the do loop will end.                  

c                   dow       sqlcod = 0         

*  While inside the do loop, we can do whatever processing we want.  We have 

* selected the records using the SQL query, and are only processing the records
* that were selected.  In this case, I am writing to file1 because the record format
* and fields are identical to file2.  

c                   write     file1                               

c/exec sql     

* Next, we just fetch the next record in and go through the do loop again.                                                    

c+  fetch C1                                                       

c+  into :FileDS                                                   

c/end-exec                                                         

c                   enddo     

* When finished, we need to close the cursor.  Leaving the cursor open will cause

* undesirable results.  If the program is called again, and it attempts to open the 

* cursor while it was already opened previously, it will cause an error.                                     

c/exec sql                                               

c+  close C1                                             

c/end-exec                                               

*                 - Finally, when all is complete, set on *INLR and end the program                                                        

c                   eval      *inlr = '1'      

I hope someone finds this useful.  Once you get the hang of it, you can do amazing things!

 

AS400 SQL to figure out what duplicate records exist in a file, but a field value contains special value

My dilemma today has been that I have to create an SQL to tell me out of a 700+ million record file, what duplicate records there are in the table, and to only select them if a certain field is a certain value.  I should be able to put some brains together tomorrow and figure this out, but if anyone out there knows the magic for this I'd be happy to hear it ahead of time so I can impress management with my technical prowess.  :)

So here's the problem:

1.  I have an AS400 file that contains more than 700 million records (growing daily).  In this file, there are 100 fields all together, and there are 5 fields that when combined, identify the record as unique (to me anyway).  There are 3 other fields besides the unique key of importance in this file.

2.  In the file, I am trying to come up with the SQL statement to retrieve all records from the file where there is not a corresponding record with the same unique key (field1 + field2 + field3 + field4 + field5), and where field6 = 'AAA' and field 7 = 'XX' and field8 = 'XX'. 

3.  The final output should give me only records that are in the table once, based on the 5 key fields, and only when field6, field7, and field8 equals the input values.

I think I am very close with the following SQL statement.  I was running late today, so didn't have time to put the finishing touches on it.

select * from file1 where                 

(field1,field2,field3,field4,field5) in      

(                                                   

select field1,field2,field3,field4,field5    

from file1                                       

group by                                            

field1,field2,field3,field4,field5           

having count(*)=1                                   

)                                                   

and field6 = 'AAA'                                   

and field7 = 'XX'                                   

and field8 = 'XX'                                                      

I think this is giving the correct information, although I need to validate it some more.  I am telling the SQL query to select all fields from file1, where the 5 unique key fields are found in the inner table one time only with the having count(*)=1 statement, grouping by the 5 key fields.  On the outer query, I am limiting the results to only those where the other 3 fields are equal to the specific values.

I will put the finishing touches on this query tomorrow (if needed), and will update my entry if anything changed.

Selecting from SQL distinct

I'm learning all sorts of fun stuff with this catastrophic customer I'm working with.  Today, I learned how to SQL an AS400 table, and get only one occurrence of a certain field per record set.  For example, I needed to find out how many different values a certain field was possible of having depending on how other fields were set.  Here's the final product:

select distinct field1 from file1 where              
(field1,field2,field3) in   
(                                                       
select field1,field2,field3 
from file1 where field2='AAA' and field1 <> 'BBB'    
group by                                                
field1,field2,field3

In the above statement, the distinct keyword does the magic.  I'm joining the file1 with itself, where field2 is equal to AAA and field1 is equal to BBB.  If field 1 is set to AAA 300 times, it will be listed in the output only once.  It will also list each other value that is set to in the table one time, so that you can easily see how many different values can occur in the field in the table.  In my case, the file had over 700 million records, so it was a fast way to get the information I needed.  I hope this information was useful!    

Joining 2 files with same record format and field definitions

Recently, I have found myself dealing with a customer which has a highly customized software system running on their AS400, which has caused me much grief.  However, during this phase I have learned quite a bit imbedded RPG SQL, and basic SQL queries.  I am mainly writing this stuff down so that I will remember in the future.  I figured it might help someone else along the way, so here goes....

Today, I applied some queries to figure out some complex data scenarios in the system.  Basically,  I needed to join 2 files together to compare them.  The files had the same record format and field names (one was basically a copy of the other, but with a different AS400 file name).  In STRSQL on the AS400, I performed statements similar to the below:

select *                                             
from file1 a                                      
join file2 b on                                     
a.field1||a.field2||a.field3 =   
b.field1||b.field2||b.field3 where
a.field4 <> b.field4                                 
order by a.field1, a.field2                          

In the above statement, I used the identifier "a" for file1, and "b" for file2.  This allows SQL to recognize which file you are referencing in the other statements.  In plain English, the above statement will join file1 & file2 together where field1, field2, & field3 concatenated together is the same between both files.  Once the join is established, only records where field4 from the first file does not equal field4 from the 2nd file.  The concatenation of fields is a very powerfull tool I didn't realize existed until recently, so I hope that information is helpful to someone.