15,962,563 members
5.00/5 (1 vote)
See more: , +
what is wrong??

SQL
SELECT A.Code , A.Name, A.Value - isnull(B.Value,0) - isnull(C.Value,0) as COLLL
FROM (SELECT [Code],[Name],Sum([Value]) as Value FROM [zamzam].[dbo].[B_Delivery_Stuff] WHERE [Type]=1 AND [Code] IN(SELECT [Code] FROM [zamzam].[dbo].[S_Stuff] WHERE ID_TypeStuff=4)
Group by Code ,Name) A
left join (SELECT [Code],Sum([Value]) as Value
FROM [zamzam].[dbo].[B_Delivery_Stuff] B WHERE [type]=2 AND Code IN(SELECT [Code]
FROM [zamzam].[dbo].[S_Stuff] where ID_TypeStuff=4)  Group by Code) B on A.Code = B.Code
left join (SELECT [Code]
,Sum([Value]) as Value
FROM [zamzam].[dbo].[B_StuffProduct] C WHERE Code IN(SELECT [Code]
FROM [zamzam].[dbo].[S_Stuff] where ID_TypeStuff=4)  Group by Code) C on A.Code = C.Code having COLLL<>0
Posted

## Solution 1

Try this.

SQL
SELECT A.Code , A.Name, A.Value - isnull(B.Value,0) - isnull(C.Value,0) as COLLL
FROM (SELECT [Code],[Name],Sum([Value]) as Value FROM [zamzam].[dbo].[B_Delivery_Stuff] WHERE [Type]=1 AND [Code] IN(SELECT [Code] FROM [zamzam].[dbo].[S_Stuff] WHERE ID_TypeStuff=4)
Group by Code ,Name) A
left join (SELECT [Code],Sum([Value]) as Value
FROM [zamzam].[dbo].[B_Delivery_Stuff] B WHERE [type]=2 AND Code IN(SELECT [Code]
FROM [zamzam].[dbo].[S_Stuff] where ID_TypeStuff=4)  Group by Code) B on A.Code = B.Code
left join (SELECT [Code]
,Sum([Value]) as Value
FROM [zamzam].[dbo].[B_StuffProduct] C WHERE Code IN(SELECT [Code]
FROM [zamzam].[dbo].[S_Stuff] where ID_TypeStuff=4)  Group by Code) C on A.Code = C.Code having A.Value - isnull(B.Value,0) - isnull(C.Value,0) <>0

dont work ...
sachin10d 19-Sep-11 13:21pm
HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.

A HAVING clause is like a WHERE clause, but applies only to groups as a whole, whereas the WHERE clause applies to individual rows. A query can contain both a WHERE clause and a HAVING clause. The WHERE clause is applied first to the individual rows in the tables . Only the rows that meet the conditions in the WHERE clause are grouped. The HAVING clause is then applied to the rows in the result set. Only the groups that meet the HAVING conditions appear in the query output. You can apply a HAVING clause only to columns that also appear in the GROUP BY clause or in an aggregate function
sachin10d 19-Sep-11 13:22pm
can you please tell the error.

## Solution 2

The "Having" keyword must go inside a perenthesis unit with either a "Group By" or one of the "Sum" functions. The position that it is at is invalid because it is not contained in either an aggregate function or the GROUP BY clause. If you intended to make it a "WHERE" clause, simply replace "HAVING" with "WHERE".

v2