Re: old query issue

I agree that RTVQMQRY is not the ultimate tool to learn SQL.

But things have changed as to what that command generates.

There used to be a problem that ALL join types were retrieved with WHERE clauses, as you say, and as type 1 JOIN types.

There were PTFs to change that - there are probably supercedes for these -

V5R4 - SI42309
6.1 - SI42345
7.1 - SI42363

At 7.1 I just created a type 2 JOIN *QRYDFN with 3 files. I then ran RTVQMQRY over it.

The SELECT statement used 2 LEFT OUTER JOINs - exactly what I would expect.

For a type 2 JOIN *QRYDFN, things are a little more interesting - they also implement it with 2 LEFT OUTER JOINs, with tests in the WHERE clause for IS NULL on columns in the 2nd and 3rd dial.

That could have been done with EXCEPTION JOINs, I guess - but at least the JOINs are written out as true SQL JOINs.

HTH
Vern

On 5/1/2015 7:09 AM, rob@xxxxxxxxx wrote:

Attempting to learn SQL by RTVQMQRY is a very bad idea.
For one thing, Query/400 will not show you any decent joins. What little
joins they show you are done via where clauses. On exception joins and
what not the RTVQMQRY just fails outright.
For another thing, getting the subtotals as shown by Query/400 is not
possible with the logic shown by RTVQMQRY. As Chuck said, you will have
to make a decision as to whether or not you want the subtotals right there
in your view by using grouping sets, ROLL UP, CUBE, and whatever that
genre is called. Or, if you want your view to get the details and your
Query can then do the subtotals off of that view.

But I do agree that view will make much more sense to many of your users
than having to figure out all the joins themselves,
calculating many derived columns (like onHand is equal to opening balance
+ receipts - issues + adjustments),
and converting many confusing 6 character column names to more meaningful
column names. MYFILA, MYFILB, MYFILC, MYFILD becomes ItemNbr, ItemDesc,
OnHand, AllocQty


Rob Berendt