RPGPGM.COM - From AS400 to IBM i › Getting the SQL statement of Query
I first learned about embedding SQL into RPG programs at a user group meeting many years ago. I could see it as a tool that would increase my productivity, but all I had was a paper copy of the slideshow that had been presented. The company I was working with had SQL loaded on their AS400 (IBM i), but none of their programmers used it. This was the time before Google, and I struggled to find resources about SQL especially about the different types of joins, etc.
Fortunately I stumbled across a way to retrieve the SQL statement from a Query/400 into a source member. Now I could build a Query close to what I want, then view the SQL to get an idea of how to make my own statement.
The RTVQMQRY command extracts the SQL statement from a Query Management, QM, query, and places it in a source member. Fortunately for the me it is also possible to do the same for a Query/400.
Retrieve Query Mgmt Query (RTVQMQRY)
In the example, above, I want to see the SQL for the Query TEST, which is in the MYLIB library. I want the source member to be created in the source file MYSRC, which is in the library MYLIB. I have left the 'Source member' parameter to be the default, *QMQRY, as I want the member to have the same name as the Query.
The important parameter is the last one, 'Allow information from QRYDFN'. Query/400 generates objects with the type QRYDFN, just as programs are PGM and files are FILE. Change this to '*YES' as object we want the SQL from is a Query/400. Press Enter and a member, with the name entered, is added to the source file.
H QM4 05 Q 01 E V W E R 01 03 13/08/28 05:00
The source, above, was extracted from a simple Query I created for this post. The first three lines contain the Query/400 specific formatting, and should be ignored. The remaining seven lines are the SQL statement. These can be taken and used elsewhere.
You can learn more about the RTVQMQRY command on the IBM website here»
This article was written for OS i 7.1, and it should work with earlier releases too.
Read the original at RPGPGM.COM - From AS400 to IBM i.