Re: Clarification of SQL VS Query Question

Comments inline

At 11:47 AM 10/10/02 -0500, you wrote:

Thanks for initial feedback.

I should clarify that our needs our strictly for the programmers, for both
reports and applications.

Regarding V5's SQL/400 toolbox that was mentioned, perhaps that is all we
need. Can we do STRSQL from command line with it?

Yes, it's part of the product, which is officially known as SQL Development
Kit and Query Manager - or was. STRSQL was what I learned SQL from, 10-11
years ago. It doesn't have all the SQL functionality in it, esp. the newer
stuff. It will recognize JOIN keywords but will not generate them, e.g.

    select field1, fileld2 from file1 join file1 on file1.field1 =
file2.field2

can be written as

    select field1, fileld2 from file1, file2 where file1.field1 = file2.field2

A type 1 join in Query 400 would be like the second one, but with aliases

    select field1, fileld2 from file1 t01, file2 t02 where t01.field1 =
t02.field2

Look familiar? Note the T01, T02?

Another initial need would be to use SQL commands in a CL in lieu of query,
for reporting, etc.

STRQMQRY can fill this role. It'll run QRYDFNs - there's a parm to use
QRYDFN(*ONLY). You need to be sure that the queries have none of the things
that don't translate directly. Chapters 11 & 12, IIRC, of Query Management
Programming, will give you a lot of help here. It's under Database on
InfoCenter. The substitution variable ability of STRQMQRY can be used with
QRYDFNs that have merge fields (a colon before the field name) on the
records select panel. But real QMQRYs can have substitution variables
anywhere. In fact, several folk, including Midrange Computing magazine,
have made general SQL statement processors, just using adjacent
substitution variables (an ampersand before a variable name).

There's also a RTVQMQRY command on the system. It'll generate a source
member for a QMQRY (Query Management Query) from a QRYDFN. There's also a
RTVQMFORM, for output.

But, in the short run, lots of things could be done, just changing to STRQMQRY.

BTW -- commercial interruption - we <www.centerfieldtechnology.com> have a
product called query/CONVERTER that runs the RTVQMQRY|FORM commands, then
puts the warning messages, etc., as comments in the resulting source.

Print output is done with QMFOMRs, which are best designed in Query Manager
- the tag language for them is rather painful to work with but is
automatically generated with Query Manager. And the interface is similar,
again, to Query 400.

OPNQRYF converts very directly to SQL, IMO.  Issues might present around
overrides, which might not be necessary anymore, unless to avoid changing
the code in RPG. The FILE parm becomes the list of files in a SELECT
statement, FORMAT is the list of fields, along with MAPFLD, to some extent.
QRYSLT becomes the WHERE clause, GRPFLD becomes GROUP BY, GRPSLT becomes
HAVING, KEYFLD becomes ORDER BY. Some of the optimization parms are handles
with other clauses in SQL statements, some in a data area called QAQQINI,
some in system & job query attributes.

Bob Carl, Senior Systems Analyst