Icon View Incident Report

Serious Serious
Reported By: Marco Antonio
Reported On: 9/20/2001
For: Version 3.01 Build 1
# 875 Using Aggregate Functions with an SQL Statement Containing a UNION Clause Does not Work

I've tried a SQL with that join's 2 similar tables using SUM to retrieve the total, and group by, but it doesn't work instead I get an "error #11949 End of selected expected but 'union' found". If I don't use the the aggregate functions and the respective GROUP BY it works fine or if i use the GROUP BY at the end of query and don't use the aggregate it works too.

select FDSC.NrFunc, FDSC.Sigla, FDSC.Periodo, FLTP1.prd_dia, FLTP1.
prd_hrs, count(FDSC.Sigla) as faltas from faltsdsc FDSC
left join flttmps FLTP1 on FDSC.Periodo = FLTP1.cod
left join pessoal PSS1 on PSS1.NrFunc = FDSC.NrFunc
left outer join Regimes RGM1 on RGM1.Cod = PSS1.Regime
where (FDSC.sigla in ('t1', 'FE', 'IJ')) and 
     (Data between '1999-9-1' and '2000-8-31') and (FDSC.periodo > 0) and 
RGM1.regimeFaltas = 1
union ALL
select FABN.NrFunc, FABN.Sigla, FABN.Periodo, FLTP1.prd_dia, FLTP1.
prd_hrs, count(FABN.Sigla) as faltas from faltsabn FABN
left join flttmps FLTP1 on FABN.Periodo = FLTP1.cod
left join pessoal PSS1 on PSS1.NrFunc = FABN.NrFunc
left outer join Regimes RGM1 on RGM1.Cod = PSS1.Regime
where (FABN.sigla in ('t1', 'FE', 'IJ')) and 
     (Data between '1999-9-1' and '2000-8-31') and (FABN.periodo > 0) and 
RGM1.regimeFaltas = 1
Group by FDSC.NrFunc, FDSC.Sigla, FDSC.Periodo



Resolution Resolution
Fixed Problem on 9/22/2001 in version 3.02 build 1
Image