IBMi (AS400) fans only – From Query to SQL


In most of IBMi we have a large number of QUERIES.

Often these QUERIES are provided to end users, with all limitations of this practice. I think this practice is deplorable.

How about turning these queries into SQL and then writing an RPG with embedded SQL?

The QUERY to SQL translation process is really simple, it only takes one command:

RTVQMQRY QMQRY(MyQueryLib/MyQuery) SRCFILE(MyLib/MySrcFile) ALWQRYDFN(*YES)

That’s it!

Below an example of a cursor declaration in an SQLRPGLE program (Sql embedded)

// preapre the data recordset

exec sql declare C1 cursor for
select OTORD00F.OTANN0,
OTORD00F.OTCOR0,
CLANA00F.CLNOM0
from OTORD00F
inner join CLANA00F on OTCCL0 = CLCCL0
where OTANN0 like :F1ANN0lke
and OTCOR0 between :F1COR0str and :F1COR0end
and CLNOM0 like :F1NOM0lke
order by OTCOR0
for read only;

Here is a complete SQLRPGLE sample program with SQL embedded.

#IBMiSample

https://www.linkedin.com/in/aldosucci/
Verified by MonsterInsights