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.
It's a very good article and very handy option to get the SQL behind the WRKQRY.
ReplyDeleteThis is useful information, thank you.
ReplyDeleteLooks like a great way of learning SQL for end uses who are fully proficient in Query
ReplyDeleteI think you're better off reading SQL manuals. WRKQRY will show all the joins as done via WHERE clauses instead of JOIN. And when you 'retrieve' an exception join it goes ill since that's doable with a WHERE clause.
ReplyDeleteThat being said, I've written a similar magazine article on RTVQMQRY about a decade or so ago so, yes, I have done it both ways.
And I use sql instead of wrkqry even on quick and dirty's because there are so many times that I want to step outside of the comfort zone of wrkqry and I don't want the slightest temptation to not use this function or that function just because it's not available in WRKQRY and I've already invested this time in WRKQRY so is it worth stepping out into sql? Nope, start out in sql and avoid that temptation.
Rob Berendt
THANKS!! This was an awesome tip! Like many shops, I have a myriad of legacy Queries that I have to maintain, but haven’t the time to convert to RPG. After reading this article, I immediately created a new source file for my Query source and new user-defined option in SEU specifying the RTVQMQRY command. In a matter of five minutes I ran the SEU option on all object type QRYDFN in my production library, and now have a searchable source file for all 955 queries in my environment. This will undoubtedly save me a ton of time when I’m looking for something in our queries.
ReplyDeleteI know you can find things using the searchable source from RTVQMQRY, because it will give you the columns and tables used, but queries should be translated one by one,with testing,before actually using the source files for a revised query. The SQL translation turns summaries into detail queries and messes up the left and exception joins. Note that there is also a RTVQMFORM.
ReplyDeleteHowever, I did use RTVQMQRY to get started in SQL, and it does help. I would start with WRKQRY and then retrieve the SQL. It is training wheels; you don't keep working that way; but it is good to start with what you know.
This is good information. Never knew.
DeleteI had no idea you could do this! Very cool!
ReplyDeleteThis is great, thanks for sharing.
ReplyDeleteGood article. I wish I knew more about embedding SQL in RPG. I haven't had time to research it on the web.
ReplyDeleteGood one -Thanks for sharing
ReplyDeleteVery interesting material - appreciate your efforts!
ReplyDeleteMaybe someone can answer this question for me. I have a dependent query that accepts variables from the CL. When using STRQMQRY with this query, it retrieves the data correctly...When I extract the SQL source, (and modify the cross joins into an inner join and change the ON statements) it errors on the WHERE clause WHERE T01.field1 = &parm1 with the error "Position 26 Token & was not valid.".
ReplyDeleteWill this process not work with dependent queries?
Ron, how are you re-running the SQL stmt after you've modified it? Some 3rd party tools will allow you to put the Query400 substitution variables but some won't Look for the SETVAR parameter to compatibility. You could also Google "SQL Query SETVAR DB2" to look for solutions.
DeleteVery good! Thank you!
ReplyDeleteHi This is nice but there is a catch it doesn't give you the output file name, i.e the output file name in the work query. How to get that, I need that too. Please help.
ReplyDeleteYou are correct it does not.
DeleteWRKQRY uses the internal Select statement to gather the result set.
then it is whatever you put into the WRKQRY that decides the type of output you desire.
You would need to look at the Query's definition to find that information.
This been a blessing to find. Converting AS400 queries to living data via PHP and ODBC has been challenging in some cases.
ReplyDeleteI've noticed that it that feature doesn't convert summary and break information when created the SQL statement.Am I wrong?
ReplyDeleteYou are not wrong. The summary and break information are part of the tool, not the SQL contained within.
DeleteI have written several other posts how you can provide summaries, etc., with just SQL.