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:
20% Off with Coupon: NICKLITTEN
I highly recommend the SNUG CBD Tincture to help keep you in the zone when programming!
In Partnership with SNUG CBD - American readers get 20% off
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 > 0
and 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. 🙂
