× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.