August 24, 2008 at 11:07 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 24, 2008 at 11:37 pm
complete guess .. but perhaps you should not use isnull with sum. Maybe you could put the results of isnull in a variable instead and use sum that way.
August 25, 2008 at 12:15 am
Can you print out the dynamic SQL instead of an execute and post the string?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 25, 2008 at 12:43 am
bodhilove (8/24/2008)
complete guess .. but perhaps you should not use isnull with sum. Maybe you could put the results of isnull in a variable instead and use sum that way.
sory, can u give a detail explain cause i don't get it, i had tried not use isnull but i get same error
August 25, 2008 at 2:27 am
GilaMonster (8/25/2008)
Can you print out the dynamic SQL instead of an execute and post the string?
i had change exec with print but the error is same, can u give another solve???? thx
August 25, 2008 at 4:47 am
pakaw_man (8/25/2008)
i had change exec with print but the error is same, can u give another solve???? thx
Very strange. The error comes from executing the dymanic SQL. If you're not executing it, there should be no way of getting an error from it.
Please change the while loop to the following.
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')
print '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) '
set @Start = @Start + 1
end
If it prints out SQL, please post that. If it gives an error, please post the error.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 25, 2008 at 10:07 am
I believe the problem is the way the UPDATE statement has a SUM() aggregation with a subquery inside of it with another SUM() aggregation. You'll probably need to rewrite this.
April 6, 2011 at 1:30 am
You can use the inner join and do the update,
something like...
UPDATE D SET InterNetConnectivity = ISNULL(CASE WHEN [HOME INTERNET CONNECTIVITY]='YES' THEN 'Y' ELSE 'N' END,'')
FROM (SELECT DISTINCT [EMPLOYEE ID],[HOME INTERNET CONNECTIVITY],[Project ID in SIS] FROM EMPPROJTEMP) A
INNER JOIN EMPLOYEE C ON A.[EMPLOYEE ID]=C.EMPLOYEECODE
INNER JOIN PROJECTRESOURCE B ON B.EMPLOYEEID=C.EMPLOYEEID and B.PROJECTID=[Project ID in SIS]
INNER JOIN ASSETDETAIL D ON B.PROJECTRESOURCEID=D.ProjectResourceID
here you can use the sum
Hope this make sense
April 6, 2011 at 2:16 am
Please note: 3 year old thread.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply