Must declare the variable '@TheYear'

sasire18

hi I copied this sql query from msdn I am getting the following

If i declare the value for the variable @TheYear I am getting the same error .Whats the matter with query any idea the query is below

SELECT o.customerid, od.orderid, SUM(od.quantity*od.unitprice) AS price
FROM Orders o, [Order Details] od
WHERE Year(o.orderdate) = @TheYear AND od.orderid=o.orderid
GROUP BY o.customerid, od.orderid
ORDER BY o.customerid

DECLARE @TheYear int
SET @TheYear = 1998

SELECT
CASE GROUPING(o.customerid) WHEN 0
THEN o.customerid ELSE '(Total)' END AS MyCustomerID,
CASE GROUPING(od.orderid) WHEN 0
THEN od.orderid ELSE -1 END AS MyOrderID,
SUM(od.quantity*od.unitprice) AS price
FROM Orders o, [Order Details] od
WHERE Year(orderdate) = @TheYear AND od.orderid=o.orderid
GROUP BY o.customerid, od.orderid WITH ROLLUP
ORDER BY o.customerid, price

sasi

Pankaj Kulkarni

Hello

sasire18 wrote:

SELECT o.customerid, od.orderid, SUM(od.quantity*od.unitprice) AS price FROM Orders o, [Order Details] od WHERE Year(o.orderdate) = @TheYear AND od.orderid=o.orderid GROUP BY o.customerid, od.orderid ORDER BY o.customerid DECLARE @TheYear int SET @TheYear = 1998

you have to declare "@TheYear" before the query but you declared it after the query. Pankaj Kulkarni

venkat2reddy

DECLARE @TheYear INT SELECT o.customerid, od.orderid, SUM(od.quantity*od.unitprice) AS price FROM Orders o, [Order Details] od WHERE Year(o.orderdate) = @TheYear AND od.orderid=o.orderid GROUP BY o.customerid, od.orderid ORDER BY o.customerid SET @TheYear = 1998 SELECT CASE GROUPING(o.customerid) WHEN 0 THEN o.customerid ELSE '(Total)' END AS MyCustomerID, CASE GROUPING(od.orderid) WHEN 0 THEN od.orderid ELSE -1 END AS MyOrderID, SUM(od.quantity*od.unitprice) AS price FROM Orders o, [Order Details] od WHERE Year(orderdate) = @TheYear AND od.orderid=o.orderid GROUP BY o.customerid, od.orderid WITH ROLLUP ORDER BY o.customerid, price check now. it will work.

Colin Angus Mackay

No. It won't. You declare @TheYear, use @TheYear, and then SET @TheYear. What is the value of @TheYear in the first SELECT statement. As the question was already answered correctly, I'm left wondering why you also answered. ColinMackay.net "Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius "If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

sasire18

I actually got my answer from Pankaj sasi