February 16, 2012 at 8:29 am
Using CTE would still produce neattier looking code...
February 16, 2012 at 8:38 am
Eugene Elutin (2/16/2012)
Using CTE would still produce neattier looking code...
CTE or not, a syntax error is a syntax error. I'm happy the OP resolved his issue but I like to know why it didn't work in the way he was describing, hence my testing.
I suspect an obscure typo that was causing the syntax error.
The probability of survival is inversely proportional to the angle of arrival.
February 16, 2012 at 8:51 am
sturner (2/16/2012)
Eugene Elutin (2/16/2012)
Using CTE would still produce neattier looking code...CTE or not, a syntax error is a syntax error. I'm happy the OP resolved his issue but I like to know why it didn't work in the way he was describing, hence my testing.
I suspect an obscure typo that was causing the syntax error.
That is why I've asked OP to provide the query which produces the error, however he avoided it. Any conspiracy theory? 😀
February 16, 2012 at 8:56 am
Eugene Elutin (2/16/2012)
sturner (2/16/2012)
Eugene Elutin (2/16/2012)
Using CTE would still produce neattier looking code...CTE or not, a syntax error is a syntax error. I'm happy the OP resolved his issue but I like to know why it didn't work in the way he was describing, hence my testing.
I suspect an obscure typo that was causing the syntax error.
That is why I've asked OP to provide the query which produces the error, however he avoided it. Any conspiracy theory? 😀
My bad I was trying to multi-task and I made a dumb mistake.
You are right about the conspiracy theory.:hehe:
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 16, 2012 at 9:01 am
So I just took your case statement and put it at the end. Can you run it and tell me if it works? You never gave us your updated query that was still not running.
SELECTCOUNT(ResCalc.CONVNO) AS RecordCount,
CASE
WHEN ResCalc.ACCDESC IN('Beef','Pork','Chicken') THEN 'Meat'
WHEN ResCalc.ACCDESC IN('Shrimp', 'Fish','Crab','Lobster') THEN 'Seafood'
WHEN ResCalc.ACCDESC IN('Orange','Apple','Pear','Plum') THEN 'Fruit'
WHEN ResCalc.ACCDESC IN('Orange Juice', 'Cranberry Juice','Grape Juice') THEN 'Juice'
WHEN REsCalc.ACCDESC IN('Other','Other Item') THEN 'Other'
WHEN ResCalc.ACCDESC IN('Theft') THEN 'Theft'
ELSE 'Not Specified'
END AS ACCDESC,
SUM(ResCalc.OUTSTANDING) AS SumOutstanding
FROM TEMPOPEN AS ResCalc
WHERE ResCalc.OUTSTANDING <0
GROUP BY CASE
WHEN ResCalc.ACCDESC IN('Beef','Pork','Chicken') THEN 'Meat'
WHEN ResCalc.ACCDESC IN('Shrimp', 'Fish','Crab','Lobster') THEN 'Seafood'
WHEN ResCalc.ACCDESC IN('Orange','Apple','Pear','Plum') THEN 'Fruit'
WHEN ResCalc.ACCDESC IN('Orange Juice', 'Cranberry Juice','Grape Juice') THEN 'Juice'
WHEN REsCalc.ACCDESC IN('Other','Other Item') THEN 'Other'
WHEN ResCalc.ACCDESC IN('Theft') THEN 'Theft'
ELSE 'Not Specified'
END
Jared
CE - Microsoft
February 16, 2012 at 9:02 am
OK, this post contains the following query:
SELECTCOUNT(ResCalc.CONVNO) AS RecordCount,
CASE
WHEN ResCalc.ACCDESC IN('Beef','Pork','Chicken') THEN 'Meat'
WHEN ResCalc.ACCDESC IN('Shrimp', 'Fish','Crab','Lobster') THEN 'Seafood'
WHEN ResCalc.ACCDESC IN('Orange','Apple','Pear','Plum') THEN 'Fruit'
WHEN ResCalc.ACCDESC IN('Orange Juice', 'Cranberry Juice','Grape Juice') THEN 'Juice'
WHEN REsCalc.ACCDESC IN('Other','Other Item') THEN 'Other'
WHEN ResCalc.ACCDESC IN('Theft') THEN 'Theft'
ELSE 'Not Specified'
END AS ACCDESC,
SUM(ResCalc.OUTSTANDING) AS SumOutstanding
FROM TEMPOPEN AS ResCalc
WHERE ResCalc.OUTSTANDING <0
GROUP BY ResCalc.ACCDESC
Does this one produces the syntax error or wrong results?
Anyway it does not contain right thing under GROUP BY...
You've been advised to do this one:
SELECTCOUNT(ResCalc.CONVNO) AS RecordCount,
CASE
WHEN ResCalc.ACCDESC IN('Beef','Pork','Chicken') THEN 'Meat'
WHEN ResCalc.ACCDESC IN('Shrimp', 'Fish','Crab','Lobster') THEN 'Seafood'
WHEN ResCalc.ACCDESC IN('Orange','Apple','Pear','Plum') THEN 'Fruit'
WHEN ResCalc.ACCDESC IN('Orange Juice', 'Cranberry Juice','Grape Juice') THEN 'Juice'
WHEN REsCalc.ACCDESC IN('Other','Other Item') THEN 'Other'
WHEN ResCalc.ACCDESC IN('Theft') THEN 'Theft'
ELSE 'Not Specified'
END AS ACCDESC,
SUM(ResCalc.OUTSTANDING) AS SumOutstanding
FROM TEMPOPEN AS ResCalc
WHERE ResCalc.OUTSTANDING <0
GROUP BY
CASE
WHEN ResCalc.ACCDESC IN('Beef','Pork','Chicken') THEN 'Meat'
WHEN ResCalc.ACCDESC IN('Shrimp', 'Fish','Crab','Lobster') THEN 'Seafood'
WHEN ResCalc.ACCDESC IN('Orange','Apple','Pear','Plum') THEN 'Fruit'
WHEN ResCalc.ACCDESC IN('Orange Juice', 'Cranberry Juice','Grape Juice') THEN 'Juice'
WHEN REsCalc.ACCDESC IN('Other','Other Item') THEN 'Other'
WHEN ResCalc.ACCDESC IN('Theft') THEN 'Theft'
ELSE 'Not Specified'
END
February 16, 2012 at 9:05 am
I did note the query you posted, and did try to duplicate it as closely as possible on a test table.
Not having your exact table on hand I could not merely cut and paste your query verbatim.
😉
perhaps the compatibility mode of your database is significant, one can only conjecture as I was unable to duplicate the syntax error you were getting.
The probability of survival is inversely proportional to the angle of arrival.
February 16, 2012 at 9:09 am
SQLKnowItAll (2/16/2012)
So I just took your case statement and put it at the end. Can you run it and tell me if it works? You never gave us your updated query that was still not running.
SELECTCOUNT(ResCalc.CONVNO) AS RecordCount,CASE
WHEN ResCalc.ACCDESC IN('Beef','Pork','Chicken') THEN 'Meat'
WHEN ResCalc.ACCDESC IN('Shrimp', 'Fish','Crab','Lobster') THEN 'Seafood'
WHEN ResCalc.ACCDESC IN('Orange','Apple','Pear','Plum') THEN 'Fruit'
WHEN ResCalc.ACCDESC IN('Orange Juice', 'Cranberry Juice','Grape Juice') THEN 'Juice'
WHEN REsCalc.ACCDESC IN('Other','Other Item') THEN 'Other'
WHEN ResCalc.ACCDESC IN('Theft') THEN 'Theft'
ELSE 'Not Specified'
END AS ACCDESC,
SUM(ResCalc.OUTSTANDING) AS SumOutstanding
FROM TEMPOPEN AS ResCalc
WHERE ResCalc.OUTSTANDING <0
GROUP BY CASE
WHEN ResCalc.ACCDESC IN('Beef','Pork','Chicken') THEN 'Meat'
WHEN ResCalc.ACCDESC IN('Shrimp', 'Fish','Crab','Lobster') THEN 'Seafood'
WHEN ResCalc.ACCDESC IN('Orange','Apple','Pear','Plum') THEN 'Fruit'
WHEN ResCalc.ACCDESC IN('Orange Juice', 'Cranberry Juice','Grape Juice') THEN 'Juice'
WHEN REsCalc.ACCDESC IN('Other','Other Item') THEN 'Other'
WHEN ResCalc.ACCDESC IN('Theft') THEN 'Theft'
ELSE 'Not Specified'
END
Your code worked.
Sorry everyone.:blush:
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 16, 2012 at 9:11 am
Welsh Corgi (2/16/2012)
Your code worked.Sorry everyone.:blush:
phew. At least we know there isn't some obscure unreported SQL server bug. :hehe:
The probability of survival is inversely proportional to the angle of arrival.
February 16, 2012 at 9:24 am
sturner (2/16/2012)
Welsh Corgi (2/16/2012)
Your code worked.Sorry everyone.:blush:
phew. At least we know there isn't some obscure unreported SQL server bug. :hehe:
AND no conspiracy! 🙂
Jared
CE - Microsoft
Viewing 10 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply