Error in Query

[RKD2005]

-- Error: A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

DECLARE @x SMALLINT
SELECT
AL1.Type,
AL3.Name,
AL3.Guid,
@x = (Sum (ISNULL(AL2."Quantity Added", 0)) - Sum (ISNULL(AL2."Quantity Taken",0)))
FROM dbo.Inv_Details AL1, dbo.Inv_Inventory_Log AL2, dbo.vResourceItem AL3
WHERE (AL3.Guid=AL1._ResourceGuid AND AL1._ResourceGuid=AL2._ResourceGuid) and
@x < 5
GROUP BY AL1.Type, AL3.Name, AL3.Guid

 

[VitoMark]

Hello,

If you don't have to know the value of @x outside the query,
you should omit @x and you should add after the GROUP BY clause a HAVING Clause:
... HAVING (Sum (ISNULL(AL2."Quantity Added", 0)) - Sum (ISNULL(AL2."Quantity Taken",0))) < 5

Otherwise you could use f.i.
Declare @x int
Set @x = (Select count(*) * 2 from mytable ....)

 

[amrita418]

Try these:

SELECT     AL1.Type,
           AL3.Name,
           AL3.Guid,
           Sum(ISNULL(AL2."Quantity Added", 0)) - Sum(ISNULL(AL2."Quantity Taken",0))
FROM       dbo.Inv_Details AL1, 
           dbo.Inv_Inventory_Log AL2, 
           dbo.vResourceItem AL3
WHERE      AL3.Guid=AL1._ResourceGuid 
           AND AL1._ResourceGuid = AL2._ResourceGuid
GROUP BY   AL1.Type, AL3.Name, AL3.Guid
HAVING     (Sum(ISNULL(AL2."Quantity Added", 0)) - Sum(ISNULL(AL2."Quantity Taken",0))) < 5

OR

SELECT     *
FROM       (SELECT     AL1.Type,
                       AL3.Name,
                       AL3.Guid,
                       Sum (ISNULL(AL2."Quantity Added", 0)) - Sum (ISNULL(AL2."Quantity Taken",0)) X
            FROM       dbo.Inv_Details AL1, 
                       dbo.Inv_Inventory_Log AL2, 
                       dbo.vResourceItem AL3
            WHERE      AL3.Guid=AL1._ResourceGuid 
                       AND AL1._ResourceGuid = AL2._ResourceGuid
            GROUP BY   AL1.Type, AL3.Name, AL3.Guid) a
WHERE      a.x < 5

Regards,
AA