Re: SQL equivalent of query/400 needed

[CRPence <crp@xxxxxxxxxxxxxxxxxxxx>]

  Having only the Join type and tests plus Record Selection tests, is 
insufficient to know how the data will be selected.  Also note that the 
Query/400 query had fully resolved the file name to a library, yet the 
given SQL query does not name a library, such that the file name will be 
located for the SQL request, according to the NAMING() specified.

  The Query/400 defaults to applying its defined collating sequence 
rules to the character comparisons; see "Specify processing options". 
Thus if "Select collating sequence" is defined to have a /language/ or 
any other shared-value sequence, then more rows can be selected for both 
the Join and the Where; as compared to a SQL query SELECT not using the 
same rules.  The ANZQRY command performed against such a *QRYDFN I 
believe will warn of this possibility.

  Some more stuff follows, but the above is the important part.

  What I believe is the most accurate conversion to SQL, given it is to 
be a purely aggregate result to match FINAL TOTALS count from the query 
definition:

   SELECT COUNT(*)
    FROM     FILE1 A
        JOIN FILE2 B
            ON A.SBKRNM = B.SRCDTA
    WHERE A.SQCDE IN ('SA', 'SB', 'SD', 'SG', 'SH')

  The results from RTVQMQRY should effectively match in results, but 
using alternate syntax for the join, whereby all selection is moved into 
the WHERE clause; the query engine pulls the join criteria from the WHERE:

   SELECT ALL selected fields from query
    FROM     FILE1 A,
             FILE2 B
    WHERE A.SBKRNM = B.SRCDTA
      AND A.SQCDE IN ('SA', 'SB', 'SD', 'SG', 'SH')

  The given [as problematic] query, included reformatted here, has the 
IN selection as part of the join clause.  AFaIK that should not matter 
for this join type, so if changing to use the WHERE instead of the JOIN 
gets different results, I think that is a problem.  If the query join 
type had been other than 1 [other than inner join], then different 
results would often be expected.

   SELECT COUNT(*)
    FROM     FILE1 A
        JOIN FILE2 B
            ON A.SBKRNM = B.SRCDTA
           AND A.SQCDE IN ('SA', 'SB', 'SD', 'SG', 'SH')

Regards, Chuck