Re: SQL Question - Merging files or result sets?

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.

<code>
create table adults (fred int, barney int, wilma int)
create table kids (bambam int, pebbles int)
insert into adults values(0, 0, 0), (0, 1, 0), (1, 0, 0)
insert into kids values(0, 0), (0, 1), (1, 0)
<code>

<code>
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 ...
</code>

<code>
with
A as (select int(0) as C, char('A') as FN, A.*
from adults A
where fred=1)
,K as (select int(0) as C, char('K') as FN, K.*
, int(0) as junk /* need added col */
from kids K
where bambam=1)
,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
union all
select * from k
union all
select * from C
order by 1 desc, 2
</code>

Regards, Chuck