April 5, 2011 at 3:36 am
Hi I was working in sybase just started working in sql-server can any one help me writing this query
(this is working fine in sybase)
getting following in sql-server :Cannot perform an aggregate function on an expression containing an aggregate or a subquery
UPDATE MemData
SET UAmt = IsNull(
(SELECT Sum((a.Amt - IsNull(a.Chrg,0) - IsNull(a.PrevYearSaveAmt,0)) -
(IsNull((SELECT Convert(Numeric(9,2),0.00)
FROM CatData x
WHERE x.CatC = b.CatC
AND x.MSASubCat = 1),
(a.MemSaveAmt + a.SchmSaveAmt))))
FROM TrnsaData a,
CatData b
WHERE a.MNum = MTL.MNum
AND a.Stat IN ( "AS","BR","RS","RD" )
AND a.ServDate >= MTL.EffDate
AND a.CatC = b.CatC
AND b.ProdCode = @ic5_ProdCode
AND a.PmtLineNum = ( SELECT Max(c.PmtLineNum)
FROM TrnsaData c
WHERE a.LineN = c.LineN
AND a.LineNum2 = c.LineNum2
AND c.Stat IN ( "AS","BR","RS","RD" ))
AND a.ServDate < IsNull((SELECT Min(EffDate)
FROM MemData d
WHERE d.MNum = MTL.MNum
AND d.ProdCode = MTL.ProdCode
AND d.EffDate > MTL.EffDate), MTL.ClearDate)), 0)
FROM MemData MTL
WHERE MTL.MNum = @ii_MNum
AND MTL.EffDate >= IsNull(
(SELECT EffDate
FROM MemData
WHERE MNum = @ii_MNum
AND EffDate = (SELECT MAX(EffDate)
FROM MemData
WHERE MNum = @ii_MNum
AND EffDate < @idt_RecalcStartDate) ), @idt_RecalcStartDate)
and .......
April 5, 2011 at 9:04 am
salil kolhatkar-423517 (4/5/2011)
Hi I was working in sybase just started working in sql-server can any one help me writing this query(this is working fine in sybase)
getting following in sql-server :Cannot perform an aggregate function on an expression containing an aggregate or a subquery
UPDATE MemData
SET UAmt = IsNull(
(SELECT Sum((a.Amt - IsNull(a.Chrg,0) - IsNull(a.PrevYearSaveAmt,0)) -
(IsNull((SELECT Convert(Numeric(9,2),0.00)
FROM CatData x
WHERE x.CatC = b.CatC
AND x.MSASubCat = 1),
(a.MemSaveAmt + a.SchmSaveAmt))))
FROM TrnsaData a,
CatData b
WHERE a.MNum = MTL.MNum
AND a.Stat IN ( "AS","BR","RS","RD" )
AND a.ServDate >= MTL.EffDate
AND a.CatC = b.CatC
AND b.ProdCode = @ic5_ProdCode
AND a.PmtLineNum = ( SELECT Max(c.PmtLineNum)
FROM TrnsaData c
WHERE a.LineN = c.LineN
AND a.LineNum2 = c.LineNum2
AND c.Stat IN ( "AS","BR","RS","RD" ))
AND a.ServDate < IsNull((SELECT Min(EffDate)
FROM MemData d
WHERE d.MNum = MTL.MNum
AND d.ProdCode = MTL.ProdCode
AND d.EffDate > MTL.EffDate), MTL.ClearDate)), 0)
FROM MemData MTL
WHERE MTL.MNum = @ii_MNum
AND MTL.EffDate >= IsNull(
(SELECT EffDate
FROM MemData
WHERE MNum = @ii_MNum
AND EffDate = (SELECT MAX(EffDate)
FROM MemData
WHERE MNum = @ii_MNum
AND EffDate < @idt_RecalcStartDate) ), @idt_RecalcStartDate)
and .......
I'd suggest breaking it up so that you delcare a variable, place the result of the subquery in it and then run your IsNull on the variable.
DECLARE @CheckMe int
SELECT @CheckMe = Sum((a.Amt - IsNull(a.Chrg,0) - IsNull(a.PrevYearSaveAmt,0)) -
(IsNull((SELECT Convert(Numeric(9,2),0.00)
FROM CatData x
WHERE x.CatC = b.CatC
AND x.MSASubCat = 1),
(a.MemSaveAmt + a.SchmSaveAmt))))
FROM TrnsaData a,
CatData b
WHERE a.MNum = MTL.MNum
AND a.Stat IN ( "AS","BR","RS","RD" )
AND a.ServDate >= MTL.EffDate
AND a.CatC = b.CatC
AND b.ProdCode = @ic5_ProdCode
AND a.PmtLineNum = ( SELECT Max(c.PmtLineNum)
FROM TrnsaData c
WHERE a.LineN = c.LineN
AND a.LineNum2 = c.LineNum2
AND c.Stat IN ( "AS","BR","RS","RD" ))
AND a.ServDate < IsNull((SELECT Min(EffDate)
FROM MemData d
WHERE d.MNum = MTL.MNum
AND d.ProdCode = MTL.ProdCode
AND d.EffDate > MTL.EffDate), MTL.ClearDate)), 0)
UPDATE MemData
SET UAmt = IsNull(@CheckMe)
etc...
You also have a lot of calculations in the WHERE clause. It won't prevent the query from running, but it does hurt reuse speed. I'd try to get those out of there if I could, either as previously selected variables or as subqueries.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply