April 7, 2008 at 3:30 am
Hello comunity
I have the following script :
select CREND = PL.CREND, NOME = PO.NOME, NCONT = PO.NCONT, EVIRS = sum(PL.EVIRS), EREC = sum(PL.EREC), U_TXIRS = isnull((select
isnull(fo.txirs,0) from fo (nolock) where fo.fostamp = PL.fcstamp),0), U_VLIRS = sum(isnull((select (case when isnull(fo.txirs,0) > 0 then PL.evirs/
isnull(fo.txirs,0) * 100 else 0 end) from fo (nolock) where fo.fostamp = pl.fcstamp),0)) from pl (nolock) inner join po (nolock) on
pl.postamp=po.postamp inner join fo on pl.fcstamp=fo.fostamp
where (convert(char(10),PL.RDATA,121) BETWEEN '2007-01-01' AND '2007-12-31' and PL.EVIRS > 0)
group by pl.crend,po.nome,po.ncont,pl.fcstamp
But , sql server return this error :
37000(130)[Microsoft][SQL Native Client][SQL Server]Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
How can resolve this problem
Many thanks
Luis Santos
April 7, 2008 at 3:46 am
I might be missing something here but I'm not sure you need those sub-queries there as you're alread joining on the table fo. See if this works:
select CREND = PL.CREND, NOME = PO.NOME, NCONT = PO.NCONT, EVIRS = sum(PL.EVIRS), EREC = sum(PL.EREC), U_TXIRS = isnull(fo.txirs,0)
, U_VLIRS = sum(case when isnull(fo.txirs,0) > 0 then PL.evirs/ isnull(fo.txirs,0) * 100 else 0 end)
from pl (nolock)
inner join po (nolock) on pl.postamp=po.postamp inner join fo on pl.fcstamp=fo.fostamp
where convert(char(10),PL.RDATA,121) BETWEEN '2007-01-01' AND '2007-12-31'
AND PL.EVIRS > 0
group by pl.crend,po.nome,po.ncont,pl.fcstamp
April 8, 2008 at 3:10 pm
Hello Karl
thanks for your reply, they work fine
bye
Luis Santos
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply