Select Case Statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select Case Statement

Author

Topic

AJCherniak
Starting Member

7 Posts

Posted - 2010-12-14 : 12:03:53

I have the following query to sum customer orders by week. When I run this query, I get "Sytax error or access violation". If I replace the question marks with getdate(), it returns data as expected, hoever, I need to be able to use a parameter that will point to cell B2 in Excel. Usually just putting a question marks works for this, but maybe not in a case statement? I'd also like the column name to be the value of the cell the parameter points to. Thanks in advance for the help.

SELECT FSE_Item.ItemNumber, FSE_Item.ItemDescription, Week1 = SUM(CASE WHEN FSE_POLine.RequiredDate BETWEEN ?-7 AND ? THEN FSE_POLine.LineItemOrderedQuantity ELSE '0' END)

FROM BIReports.dbo.FSE_Item FSE_Item, BIReports.dbo.FSE_POHeader FSE_POHeader, BIReports.dbo.FSE_POLine FSE_POLine

WHERE FSE_POLine.POHeaderKey = FSE_POHeader.POHeaderKey AND FSE_POLine.ItemKey = FSE_Item.ItemKey AND ((FSE_POHeader.VendorID='v10259'))

GROUP BY FSE_Item.ItemNumber, FSE_Item.ItemDescription

ORDER BY FSE_Item.ItemDescription

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2010-12-14 : 13:14:41

Enter a valid date in cell B2 and refresh the data. If that does not fix it, on the External Data toolbar in Excel (Excel 2003), click Query Parameters button. The window that comes up will allow you to specify the cell(s) from which to pick the value of each parameter. In your case you have two parameters; you will need to specify both.

AJCherniak
Starting Member

7 Posts

Posted - 2010-12-14 : 13:31:39

I'm using Excel 2010 and MS Query to do this. There is a valid date in there as far as I can tell. My IT guy finally got back to me and said that SQL doesn't like using variables of variables. He explained to me that using BETWEEN along with fixed values is already a variable...and making that point to a cell in Excel makes it a variable of a variable. He says I need to use > and < in the case statement instead of the BETWEEN function. Alternatively, using DECLARE and SET would allow me to do what I want, but Excel doesn't seem to accept that (even though MS Query will).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources