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) Type choices, press Enter. Query management query . . . . . test Library . . . . . . . . . . . mylib Source file . . . . . . . . . . mysrc Library . . . . . . . . . . . mylib Source member . . . . . . . . . *QMQRY Allow information from QRYDFN . *yes
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 V 1001 050 V 5001 004 *HEX SELECT ALL T01.ORDNBR, T02.ORDSEQ, T02.STATUS, T01.JOBNBR, T01.CUSTNO, T02.WRKCTR, T02.STRDTE FROM PRODLIB/ORDHDRP T01 LEFT OUTER JOIN PRODLIB/ORDDTLP T02 ON T01.ORDNO = T02.ORDNO ORDER BY T01.ORDNO ASC, T02.OPSEQ ASC
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 IBM i 7.1, and it should work with earlier releases too.