Hi

I have the following query:

Select distinct FK_ADRESSE_UUID from ADU.UTADU_DET_PRAEMIE where UNTERBRANCHE in ( 93001,93006,93009,98020,98021,98027,99020,99021,99 023,99024,99027,99028,99029 ) and STATUS <> 4 and (PLZ='8600')
UNION
Select distinct FK_ADRESSE_UUID from ADU.UTADU_VERTRAG where BRANCHE in ( 94,52010,88000,89000 ) and STATUS <> 4 and (PLZ='8600')

How can I get the number of rows of the result WITHOUT use of a temporary table ?

(UDB 8.1, Unix)

Marc

Marc,

Try:

with temp1 as (Select distinct FK_ADRESSE_UUID from ADU.UTADU_DET_PRAEMIE where UNTERBRANCHE in ( 93001,93006,93009,98020,98021,98027,99020,99021,99
023,99024,99027,99028,99029 ) and STATUS <> 4 and (PLZ='8600')
UNION
Select distinct FK_ADRESSE_UUID from ADU.UTADU_VERTRAG where BRANCHE in ( 94,52010,88000,89000 ) and STATUS <> 4 and (PLZ='8600') ) select count(*) from temp1;

HTH

Andy

Hi,

Solution of ARwinner also generates temporaly table.

I thing there is no way you can do it without temporaly table.

Hope this helps,
Grofaty

tip: remove the DISTINCT from each of the subqueries in the union, it is not necessary because UNION itself removes duplicate rows

does this work? i don't have db2 to test on --

Thanks, it works like this:

select count(*) from (select FK_ADRESSE_UUID from ADU.UTADU_DET_PRAEMIE where UNTERBRANCHE in ( 93001,93006,93009,98020,98021,98027 , 99020,99021,99023,99024,99027,99028,99029 ) and STATUS <> 4 and PLZ='8600'union select FK_ADRESSE_UUID from ADU.UTADU_VERTRAG where BRANCHE in ( 94,52010,88000,89000 ) and STATUS <> 4 and PLZ='8600' ) as T

-> I had to put "as T" at the end of your statement

Marc