Convert QUERY to IBM i SQL
This week I be mainly…. working on a Casino System upgrade, dragging decades old code into the twenty first century. Old RPG3, RPG400, CLP programs and techniques using outdated OPNQRYF and QUERIES are fighting back tooth and nail. I love this schnizzle. 🙂
One of the techniques for upgrading/converting older QUERY400 reports to modern SQL format is IBM i’s very cool RTVQMQRY command.
I had completely forgotten about this technique (possibly because my 32k system 38 brain has a memory leak) but it was so simple and quick I had to write it down before I had a mental buffer overload.
IBM i QUERY
A while ago I made a QUERY400 for a Software Tester who wanted to see a hotel check-in date from an Agilysys LMS File (GIP) which stores dates in the 5 digit HYD (hundred year format). But, of course, our tester wanted to check the dates in human friendly USA standard date format (M/D/YY)
So I made a quick and dirty QUERY400 to basically list the dates, create a new mapped date showing it in *USA format:
and then run the query and see a nice simple data screen like this:
From QUERY to SQL
Now if we want to modernize our approach and do this with a SQL statement it’s as easy as pie!
We simply use the RTVQMQRY command to suck that QUERY400 definition and spit out a source file with a lovely SQL statement in it that looks like this:
RTVQMQRY QMQRY(LITTENN/GIPCHECK) SRCFILE(LITTENN/QSQLSRC) ALWQRYDFN(*YES)and this generates this lovely piece of source code:
which as you can see, has this line of SQL in it:
SELECT ALL CIDTGI, CITMGI, DATE(CIDTGI+DAYS('12/31/1899')) AS
CHECKINDAT, CIAGGI, FNAMGI, KYDTGI
FROM LMDTA15/GIP T01
WHERE CIDTGI > 0and we can run that in SQL to see the exact same result.
So, next time a grey haired AS400 developer tells you that it’s a QUERY and it can’t be changed… you can raise an eyebrow and mutter about IBM i RTVQMQRY. 🙂
Hello from Abel Willium, Great Blog!
IBM i Modernization can help businesses in many ways. With the power of modernization, any business can enrich its customer experience. And there is no need to explain the immense importance of security and reliability it offers. The blog adds something new to my existing stock of knowledge.
Thanks.