January 27, 2012 at 4:14 am
Hi Friends
I have a issue which i am not aware bec myself getting surprise to it..
I have a where condition for a year based on case i need to select some IDs
eg
Select Description
From TableA
Where Case IDs When Year=2011 Then IDs (1,2,3,4,5)
When Year = 2012 Then IDs (3,4,5)
now i am getting error saying incorrect syntax near keyword in..
so is it that i cant you in statement when i am going with Case statement
Thanks & Regards
Syed Sami Ur Rehman
SQL-Server (Developer)
Hyderabad
Email-sami.sqldba@gmail.com
January 27, 2012 at 4:21 am
This was removed by the editor as SPAM
January 27, 2012 at 4:24 am
sami.sqldba (1/27/2012)
Hi FriendsI have a issue which i am not aware bec myself getting surprise to it..
I have a where condition for a year based on case i need to select some IDs
eg
Select Description
From TableA
Where Case IDs When Year=2011 Then IDs (1,2,3,4,5)
When Year = 2012 Then IDs (3,4,5)
now i am getting error saying incorrect syntax near keyword in..
so is it that i cant you in statement when i am going with Case statement
Try something like this instead
BEGIN TRAN
--Standard TestEnvironment of 1,000,000 rows of random-ish data
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment
END
--1,000,000 Random rows of data
SELECT TOP 1000000 IDENTITY(INT,1,1) AS ID,
RAND(CHECKSUM(NEWID())) * 30000 + CAST('1945' AS DATETIME) AS randomDate,
ABS(CHECKSUM(NEWID())) AS randomBigInt,
(ABS(CHECKSUM(NEWID())) % 100) + 1 AS randomSmallInt,
RAND(CHECKSUM(NEWID())) * 100 AS randomSmallDec,
RAND(CHECKSUM(NEWID())) AS randomTinyDec,
RAND(CHECKSUM(NEWID())) * 100000 AS randomBigDec,
CONVERT(VARCHAR(6),CONVERT(MONEY,RAND(CHECKSUM(NEWID())) * 100),0) AS randomMoney
INTO #testEnvironment
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3
SELECT *
FROM #testEnvironment
WHERE (randomSmallInt IN (1,2,3,4,5) AND (randomDate >= '2011-01-01' AND randomDate < '2012-01-01'))
OR (randomSmallInt IN (3,4,5) AND (randomDate >= '2012-01-01' AND randomDate < '2013-01-01'))
ROLLBACK
Stewart "Arturius" Campbell (1/27/2012)
Try a slightly different approachFrom what your sample shows, you need conditional WHERE clauses, e.g.
SELECT DescriptionFROM TableA
WHERE ((IDs IN (1,2,3,4,5)
AND YEAR = 2011)
OR (IDs IN (3,4,5)
AND Year = 2012))
Blergh, I'm too slow. However, note that the actual code produced is pretty much the same (only difference is that I removed your "YEAR" function and instead used a more efficient form. Actually, only difference is that I can't read!!)
January 27, 2012 at 4:49 am
Thanks both you gave me better picture for the result ... but today i came to know that case statement cant have In clause
Thanks & Regards
Syed Sami Ur Rehman
SQL-Server (Developer)
Hyderabad
Email-sami.sqldba@gmail.com
January 27, 2012 at 5:07 am
This was removed by the editor as SPAM
January 27, 2012 at 5:10 am
Hi All
I feeling very odd to ask but i am really really very confused by this how to make order by when query have union or union all
Thanks & Regards
Syed Sami Ur Rehman
SQL-Server (Developer)
Hyderabad
Email-sami.sqldba@gmail.com
January 27, 2012 at 5:32 am
This was removed by the editor as SPAM
January 27, 2012 at 6:02 am
sami.sqldba (1/27/2012)
Hi AllI feeling very odd to ask but i am really really very confused by this how to make order by when query have union or union all
Just wanted to add my 2 cents.
BEGIN TRAN
SELECT N
INTO #TEST1
FROM (VALUES(2),(1),(10),(7))a(N)
SELECT N
INTO #TEST2
FROM (VALUES(3),(1),(19),(4))a(N)
--Option 1
SELECT N
FROM #TEST1
UNION ALL
SELECT N
FROM #TEST2
ORDER BY N
--Option 2
SELECT N
FROM (SELECT N
FROM #TEST1
UNION ALL
SELECT N
FROM #TEST2) innerQuery
ORDER BY N
ROLLBACK
Option 1 is what Stewart suggested, which works fine. However, as a personal preference I prefer option 2 as I find it easier to read.
January 27, 2012 at 7:14 am
This was removed by the editor as SPAM
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply