​ Close
Login
Menu
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.
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:
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. 🙂
IBM i Software Developer, Digital Dad, AS400 Anarchist, RPG Modernizer, Alpha Nerd and Passionate Eater of Cheese and Biscuits. Nick Litten Dot Com is a mixture of blog posts that can be sometimes serious, frequently playful and probably down-right pointless all in the space of a day. Enjoy your stay, feel free to comment and in the words of the most interesting man in the world: Stay thirsty my friend.
AS400 QMQRY – Query Management Queries
How to Install IBM Access Client Solutions (ACS)
IBM i Data Obfuscation – Making Data Foggy Murky and Squinty
How to rename Fresche (BCD) Presto Library – XL_PRESTO
What is AS400 modernization?
IBM i ACS 5250 EMULATOR FONT – and other ridiculous mumbo jumbo
IBM i SQL statement to convert or compare hundred year date format
How to compare ‘100 Year Date’ to a Timestamp – aka – Weird AS400 iSeries Date formats
Use IBM i SQL to validate email addresses in Customer Master File