For the case of COUNT() being required, and that the selection is of
either or both files, the better choice than the CTE of the UNION is to
make CTE for each or both SELECT to be combined, counted and ordered.
Below I have included two simple TABLE creates with data where the
second has one less field. The union brings in the count in a an
additional row, the first row by initial ordering, but the desired
ordering can still be effected for each remaining row in the result set.
The pseudo-code gives an idea of how one might build the statements
to be combined [the count mostly omitted, but same methodology] to
select from either or both files, and to include the count [of the
static data; which is desirable to avoid counting since-updated live data].
The full sql WITH ... SELECT ... SELECT UNION ALL ... is functional
as written to show what the combined statements would look like for
selection from both files. It should be easy to see how it would be
reduced for either single table.
BldSlt_A: return 'fred=' + intasc + 'AND ...'
BldSlt_K: return 'bambam=' + iasc + 'AND ...'
BldCTE_A: 'A as (select int(0) as C,' +
' char(''A'') as FN, A.* from adults A' +
' where ' + BldSlt_A() + ')'
BldCTE_K: 'K as (select int(0) as C,' +
' char(''K'') as FN, K.* from kids K' +
' where ' + BldSlt_K() + ')'
If SearchingA & SearchingK Then
SearchString='with ' + BldCTE_A() + ',' + BldCTE_K +
BldCTE_C + ...
Else If SearchingA Then
SearchString='with ' + BldCTE_A() + BldCTE_C + ...
Else If SearchingK Then ...
A as (select int(0) as C, char('A') as FN, A.*
from adults A
,K as (select int(0) as C, char('K') as FN, K.*
, int(0) as junk /* need added col */
from kids K
,C as ( select int((select count(*) from a)
+(select count(*) from k)) as C
, cast(null as char) as FN,
, cast(null as int) as P1
, cast(null as int) as P2
, cast(null as int) as P3
from qsys2.qsqptabl )
select * from a
select * from k
select * from C
order by 1 desc, 2
This thread ...
Re: SQL Question - Merging files or result sets?, (continued)
This mailing list archive is Copyright 1997-2019 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