May 23

1 comments

Modernize AS400 iSeries Query – Convert to IBM i SQL

By NickLitten

May 23, 2018

#Query, #QUERY400, #RTVQMQRY

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:

Modernize AS400 iSeries Query - Convert to IBM i SQL
Modernize AS400 iSeries Query - Convert to IBM i SQL
Modernize AS400 iSeries Query - Convert to IBM i SQL
Modernize AS400 iSeries Query - Convert to IBM i SQL
Modernize AS400 iSeries Query - Convert to IBM i SQL

and then run the query and see a nice simple data screen like this:

Modernize AS400 iSeries Query - Convert to IBM i SQL

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:

Modernize AS400 iSeries Query - Convert to IBM i SQL

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.  🙂

  • 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.

    I also write in the same niche. You can visit here-https://www.integrativesystems.com/ibmi-modernization-company/. It would definitely enhance your existing stock of knowledge. Thanks.

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    Join the IBM i Community for FREE Presentations, Lessons, Hints and Tips

    >