March 4, 2010 at 12:42 pm
I'm trying to use a case statement, and inside I need to have a BEGIN and END with multiple statements, but I can't seem to get it to work:
CASE item
WHEN 2 THEN 5
ELSE 1
END
Instead of returning 5, I need to get a count. To simplify, when I Do this I get a syntax error:
CASE item
WHEN 2 THEN
BEGIN
5 -- in here I need to Select a count from a different table
END
ELSE 1
END
Is this possible?
March 4, 2010 at 1:13 pm
tnocella (3/4/2010)
I'm trying to use a case statement, and inside I need to have a BEGIN and END with multiple statements, but I can't seem to get it to work:CASE item
WHEN 2 THEN 5
ELSE 1
END
Instead of returning 5, I need to get a count. To simplify, when I Do this I get a syntax error:
CASE item
WHEN 2 THEN
BEGIN
5 -- in here I need to Select a count from a different table
END
ELSE 1
END
Is this possible?
Is this what you are looking for?
SELECT CASE WHEN 1 = 1 THEN COUNT(*)
ELSE 0
END AS Cnt
FROM sys.syscolumns
Cheers,
J-F
March 4, 2010 at 1:16 pm
Sorry, I read your post again, and you made it clear you wanted to select from another table.
In this case, you have to add parenthesis around your select :
SELECT TOP 1
CASE WHEN 1 = 1 THEN ( SELECT COUNT(*)
FROM sys.tables
)
ELSE 0
END AS Cnt
FROM sys.columns
Cheers,
J-F
March 4, 2010 at 3:16 pm
wow, that's really cool. i had no idea you could do that. Didn't seem logical to even be able to.
Since u can do that i suppose u can do the below as well.
SELECT
CASE WHEN 1 = 1 THEN ( SELECT COUNT(*)
FROM sys.columns C
where C.object_id=T.object_id
)
ELSE 0
END AS Cnt
FROM sys.tables T
where object_id=1099150961
tHIS WOULD HAVE SEVERED ME A LOT OF CODE IN THE PAST....
This is a very very functional design i like it. THANKS!!!!
March 4, 2010 at 3:19 pm
WOW. THIS IS FUN...CHECK THIS OUT
SELECT TOP 20
NAME
, T.object_id
,
CASE WHEN 1 = 1 THEN ( SELECT COUNT(*)
FROM sys.columns C
where C.object_id=T.object_id
GROUP BY C.object_id
)
ELSE 0
END AS Cnt
FROM sys.tables T
GROUP BY NAME
, T.object_id
March 4, 2010 at 4:17 pm
@BaldingLoopMan : That is exactly the same as this...
SELECT TOP 20
NAME,
object_id,
( SELECT COUNT(*) FROM sys.columns C where C.object_id=T.object_id ) AS Cnt
FROM sys.tables T
There is nothing in there that requires or benefits from a CASE statement....and all that grouping...
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);March 4, 2010 at 5:12 pm
What you should be doing is:
CASE WHEN Item = 5 THEN (SELECT {column} FROM table WHERE ...)
ELSE 1
END
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply