Re: Converting a Query to RPG

["Steve Landess" <steve_landess@xxxxxxxxxxx>]

> Rob wrote:

> RTVQMQRY.

> Amitava wrote:
> Hi. Thanks. But actually I did not want to do it in those ways (WRKQRY,
then
> print etc). I mean, I thought all the information should be some where in
> the system (some file ?). A generic program will read that and generate
> the RPG source for the query. It will be a generic program, that will
> convert any query to a RPG.
>
> I tried CRTQMQRY. But that did not give all information and whatever it
> gave is not in a specific format. Or did I do some thing wrong?

Folks -

I would strongly advise against using RTVQMQRY to retrieve the query source
for a query unless you first visually inspect the  query using option 2 or 5
within WRKQRY to determine what it does.

To see why I make this statement, create a query that joins two files using
option 3 (Unmatched records with primary file).  Then retrieve the source
for this query using RTVQMQRY and see what you get.  You'll find that it
doesn't retrieve the SQL statement that you would expect.

Example:

F9201 is one of  the JDE data dictionary files.  I wanted to see which
fields are defined in the data dictionary in the development environment
that DON'T exist in the production environment.  F9201 is uniquely keyed by
field FRDTAI,  so I create a query that joins F9201 in development to F9201
in production, specifying join on T01.FRDTAI = T02.FRDTAI, showing
_unmatched_ records.

I used RTVQMQRY to retrieve source from this query.

I would have expected something like:

SELECT * FROM ensdt1/f9201 WHERE frdtai NOT in (select frdtai from
ensdpr/f9201)


OR

select * from ensdt1/f9201 D where NOT exists
(select *from ensdpr/f9201 P where d.frdtai=p.frdtai)

However, here is what RTVQMQRY gave me:

H QM4 05 Q 01 E V W E R 01 03 03/05/16 17:49
V 1001 050 recs ENSDT1/F9201 that are NOT in ENSDPR/F9201
V 5001 004 *HEX
SELECT
  ALL       T01.FRDTAI, T01.FRCLAS, T01.FRDTAT, T01.FRDTAS, T01.FRDTAD,
            T01.FRPDTA, T01.FRARRN, T01.FRDVAL, T01.FRLR, T01.FRCDEC,
            T01.FRDRUL, T01.FRDRO1, T01.FRERUL, T01.FRERO1, T01.FRERO2,
            T01.FRHLP1, T01.FRHLP2, T01.FRNNIX, T01.FRNSY, T01.FRRLS,
            T01.FRUSER, T01.FRUPMJ, T01.FRPID, T01.FRJOBN, T01.FRUPMT,
            T02.FRDTAI, T02.FRCLAS, T02.FRDTAT, T02.FRDTAS, T02.FRDTAD,
            T02.FRPDTA, T02.FRARRN, T02.FRDVAL, T02.FRLR, T02.FRCDEC,
            T02.FRDRUL, T02.FRDRO1, T02.FRERUL, T02.FRERO1, T02.FRERO2,
            T02.FRHLP1, T02.FRHLP2, T02.FRNNIX, T02.FRNSY, T02.FRRLS,
            T02.FRUSER, T02.FRUPMJ, T02.FRPID, T02.FRJOBN, T02.FRUPMT
  FROM      ENSDT1/F9201 T01,
                   ENSDPR/F9201 T02
  WHERE     T01.FRDTAI = T02.FRDTAI

This query _joins_ the two files and produces a report which shows the
fields that exist in _both_ files, which basically is the _opposite_ of the
original query... ;>(

Hope this helps (probably not!),

Steve Landess
Austin, Texas
(512) 423-0935