August 24, 2008 at 11:06 pm
i have procedure like this:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[S_RptCustProd]
@StartPrd varchar(6),
@EndPrd varchar(6),
@Class varchar(15),
@Tipe varchar(1)
as
if exists(select * from tempdb.dbo.sysobjects where id=object_id('tempdb.dbo.#summary'))
drop table #summary
create table #summary (
CustNo varchar(20) null,
CustName varchar(100) null,
Industry varchar(50) null
)
declare @Start int, @End int, @ColName varchar(50), @StartStr varchar(50), @InvoicePajak varchar(50)
set @Start = left(@StartPrd,4)
set @End = left(@EndPrd,4)
insert into #summary (CustNo, CustName, Industry)
select distinct B.CustNo, B.CustName, B.Industry
from SalesData A
left outer join MsCustomer B on A.CustNo = B.CustNo
where A.InvoiceDate between @StartPrd+'01' and dateadd(dd,-1,dateadd(mm,1,@EndPrd+'01'))
and A.ItemClass like @Class + '%'
and A.CustNo like @Tipe + '%'
while @Start <= @End begin
set @ColName = 'C' + convert(varchar,@Start)
set @StartStr = convert(varchar,@Start)
exec ('alter table #summary add ' + @ColName + ' numeric(17,2) null')
exec ('update #summary set ' + @ColName + ' = isnull(' +
'(select sum(isnull(OriPrice,0)-isnull(OriDisc,0)-isnull(OriOutSource,0)- isnull((select sum(OriPPh) from PPh where InvoicePajak = '''+@InvoicePajak+''' and Period between '''+@StartPrd+''' and '''+@EndPrd+'''),0)) ' +
' from SalesData B ' +
' where #summary.CustName = B.Customer ' +
' and B.InvoiceDate between ''' + @StartPrd + '01'' and dateadd(dd,-1,dateadd(mm,1,''' + @EndPrd + '01'')) ' +
' and year(B.InvoiceDate) = ' + @StartStr + ' ' +
' and B.ItemClass like ''' + @Class + '%'' ' +
' and B.CustNo like '''+ @Tipe + '%''),0) ') /*+
' group by Customer),0) ')*/
set @Start = @Start + 1
end
select * from #summary
and there is an error like this:
Msg 130, Level 15, State 1, Line 1
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
please help me to solve this... thanx
August 25, 2008 at 7:31 am
You can't have this line in your second EXEC in the WHILE:
- isnull((select sum(OriPPh) from PPh where InvoicePajak = '''+@InvoicePajak+''' and Period between '''+@StartPrd+''' and '''+@EndPrd+'''),0)
That is is aggregate in a subquery that includes an aggregate. You need to do that using a derived table that returns the aggregate in your from clause.
If you explain what you need to do someone may be able to provide a simpler solution.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply