May 23, 2012 at 10:36 am
Gooood day experts!
Can anyone help me with this case statement please? The part I'm having trouble with is the very last one when the parameter @isActive = 9.
Sample
CREATE TABLE #tmp(
[Id] [varchar](15) NOT NULL,
[Order] [int] NOT NULL,
[IsActive] [bit] NOT NULL,
[Cnt] [int] NULL
)
INSERT INTO #tmp VALUES ('WAG-3218-UK',1,0,1)
INSERT INTO #tmp VALUES ('WAG-3218-UK',2,0,2)
INSERT INTO #tmp VALUES ('WAG-3218-UK',3,1,6)
INSERT INTO #tmp VALUES ('WAG-3218-UK',4,1,6)
INSERT INTO #tmp VALUES ('WAG-3218-UK',5,0,9)
INSERT INTO #tmp VALUES ('WAG-3218-UK',6,0,7)
INSERT INTO #tmp VALUES ('WAG-3218-UK',7,1,3)
INSERT INTO #tmp VALUES ('WAG-3218-UK',8,0,3)
INSERT INTO #tmp VALUES ('WAG-3218-UK',9,1,8)
INSERT INTO #tmp VALUES ('WAG-3218-UK',10,0,6)
Expected
-- If param @isActive = 0
SELECT 'WAG-3218-UK' as [Id] ,1 as [Order], 0 AS [IsActive], 1 AS InactiveCnt, 'N/A' AS ActiveCnt
UNION
SELECT 'WAG-3218-UK' as [Id] ,2 as [Order], 0 AS [IsActive], 2 AS InactiveCnt, 'N/A' AS ActiveCnt
UNION
SELECT 'WAG-3218-UK' as [Id] ,5 as [Order], 0 AS [IsActive], 9 AS InactiveCnt, 'N/A' AS ActiveCnt
UNION
SELECT 'WAG-3218-UK' as [Id] ,6 as [Order], 0 AS [IsActive], 7 AS InactiveCnt, 'N/A' AS ActiveCnt
UNION
SELECT 'WAG-3218-UK' as [Id] ,8 as [Order], 0 AS [IsActive], 3 AS InactiveCnt, 'N/A' AS ActiveCnt
UNION
SELECT 'WAG-3218-UK' as [Id] ,10 as [Order], 0 AS [IsActive], 6 AS InactiveCnt, 'N/A' AS ActiveCnt
-- If param @isActive = 1
SELECT 'WAG-3218-UK' as [Id] ,3 as [Order], 1 AS [IsActive], 'N/A' AS InactiveCnt, 6 AS ActiveCnt
UNION
SELECT 'WAG-3218-UK' as [Id] ,4 as [Order], 1 AS [IsActive], 'N/A' AS InactiveCnt, 6 AS ActiveCnt
UNION
SELECT 'WAG-3218-UK' as [Id] ,7 as [Order], 1 AS [IsActive], 'N/A' AS InactiveCnt, 3 AS ActiveCnt
UNION
SELECT 'WAG-3218-UK' as [Id] ,9 as [Order], 1 AS [IsActive], 'N/A' AS InactiveCnt, 8 AS ActiveCnt
-- If param @isActive = 9
SELECT 'WAG-3218-UK' as [Id] ,1 as [Order], 0 AS [IsActive], '1' AS InactiveCnt, 'N/A' AS ActiveCnt
UNION
SELECT 'WAG-3218-UK' as [Id] ,2 as [Order], 0 AS [IsActive], '2' AS InactiveCnt, 'N/A' AS ActiveCnt
UNION
SELECT 'WAG-3218-UK' as [Id] ,5 as [Order], 0 AS [IsActive], '9' AS InactiveCnt, 'N/A' AS ActiveCnt
UNION
SELECT 'WAG-3218-UK' as [Id] ,6 as [Order], 0 AS [IsActive], '7' AS InactiveCnt, 'N/A' AS ActiveCnt
UNION
SELECT 'WAG-3218-UK' as [Id] ,8 as [Order], 0 AS [IsActive], '3' AS InactiveCnt, 'N/A' AS ActiveCnt
UNION
SELECT 'WAG-3218-UK' as [Id] ,10 as [Order], 0 AS [IsActive], '6' AS InactiveCnt, 'N/A' AS ActiveCnt
UNION
SELECT 'WAG-3218-UK' as [Id] ,3 as [Order], 1 AS [IsActive], 'N/A' AS InactiveCnt, '6' AS ActiveCnt
UNION
SELECT 'WAG-3218-UK' as [Id] ,4 as [Order], 1 AS [IsActive], 'N/A' AS InactiveCnt, '6' AS ActiveCnt
UNION
SELECT 'WAG-3218-UK' as [Id] ,7 as [Order], 1 AS [IsActive], 'N/A' AS InactiveCnt, '3' AS ActiveCnt
UNION
SELECT 'WAG-3218-UK' as [Id] ,9 as [Order], 1 AS [IsActive], 'N/A' AS InactiveCnt, '8' AS ActiveCnt
cheers.
__________________________
Allzu viel ist ungesund...
May 23, 2012 at 10:50 am
I have no idea what the @param has to do with it but the following query will produce the results you stated you need.
select *,
case IsActive when 0 then 'N/A' else cast(Cnt as varchar(10)) end as ActiveCnt
from #tmp
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 23, 2012 at 11:05 am
Sean Lange (5/23/2012)
I have no idea what the @param has to do with it but the following query will produce the results you stated you need.
select *,
case IsActive when 0 then 'N/A' else cast(Cnt as varchar(10)) end as ActiveCnt
from #tmp
The @isActive is a stored proc parameter and can be 0 or 1 or 9. If it's not clear I'll try explain this in more detail..thought the sample above would be self-explanatory..
__________________________
Allzu viel ist ungesund...
May 23, 2012 at 11:17 am
So did my query answer what you needed? You said you had the first two figured out?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 23, 2012 at 11:17 am
I think part of the problem is that your query doesn't have a Case statement in it anywhere, and doesn't need one.
What you need is something like:
select *
from #temp
where (@isActive = IsActive or @isActive = 9);
Depending on your data distribution, and a couple of other factors, this will work, but might be slow. If it's slow, research "parameter sniffing" and "catch-all queries" for data on how to optimize it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 23, 2012 at 11:22 am
Try something like that:
select
[Id],
[Order],
[IsActive],
(case IsActive when 0 then Cnt else null end) as InactiveCnt,
(case IsActive when 0 then null else Cnt end) as ActiveCnt
from #tmp
where (IsActive = @IsActive) or (@IsActive = 9)
Hope this helps.
May 23, 2012 at 11:23 am
I think this is what he was after.
select
*,
case IsActive when 0 then cast(Cnt as varchar(10)) else 'N/A' end as InActiveCnt,
case IsActive when 1 then cast(Cnt as varchar(10)) else 'N/A' end as ActiveCnt
from
#tmp
where
(IsActive = @param) or (@param = 9);
May 23, 2012 at 6:10 pm
CELKO (5/23/2012)
This is not a table; What is the key? We do not use reserved words for names, we do not use bit flags (that was assembly language. I know this is a skeleton, but he does not have to have broken bones 🙂CREATE TABLE Foobar
(foo_name VARCHAR(15) NOT NULL,
something_seq INTEGER NOT NULL PRIMARY KEY,
something_status SMALLINT NOT NULL,
something_cnt INTEGER NOT NULL);
But this is still wrong. A status is a state of being, so it needs a time period in the form of (start_date, end_date) pairs. This is basic data modeling but I will skip it for now.
T-SQL has had the ANSI syntax for several years; you need to catch up on your MS education:
INSERT INTO Foobar
VALUES ('WAG-3218-UK', 1, 0, 1),
('WAG-3218-UK', 2, 0, 2),
('WAG-3218-UK', 3, 1, 6),
('WAG-3218-UK', 4, 1, 6),
('WAG-3218-UK', 5, 0, 9),
('WAG-3218-UK', 6, 0, 7),
('WAG-3218-UK', 7, 1, 3),
('WAG-3218-UK', 8, 0, 3),
('WAG-3218-UK', 9, 1, 8),
('WAG-3218-UK', 10, 0, 6);
The rest of your post made no sense. Where did this @active_flag come from? How can a BIT be 9? Why are strings being used as integer? SQO is a strongly typed language; data types do not magically change based on flags. We also do not do display formatting in the query.
Try this; pass a NULL to get everything.
CREATE PROCEDURE Fetch_Foobars
(@in_active_flag SMALLINT)
AS
SELECT foo_name, something_seq, something_status, something_cnt
FROM Foobar
WHERE something_status
= COALESCE (@in_active_flag, something_status);
You are not writing SQL yet, just faking your original weakly typed language with SQL.
You're right, Mr. Celko, MS SQL Server does support ANSI SQL syntax, and while the following code will work in SQL Server 2008, it won't work in SQL Server 2005 which some of us are still using and supporting.
INSERT INTO Foobar
VALUES ('WAG-3218-UK', 1, 0, 1),
('WAG-3218-UK', 2, 0, 2),
('WAG-3218-UK', 3, 1, 6),
('WAG-3218-UK', 4, 1, 6),
('WAG-3218-UK', 5, 0, 9),
('WAG-3218-UK', 6, 0, 7),
('WAG-3218-UK', 7, 1, 3),
('WAG-3218-UK', 8, 0, 3),
('WAG-3218-UK', 9, 1, 8),
('WAG-3218-UK', 10, 0, 6);
So I am glad that the OP posted code that would work directly in SQL Server 2005 WITHOUT modification.
Now, please, just gallop off into the sunset and leave us alone.
May 24, 2012 at 3:26 am
Thanks ALL for your time,
Lynn, SPOT ON!! cheers!!
__________________________
Allzu viel ist ungesund...
May 24, 2012 at 8:03 am
CELKO (5/23/2012)
This is not a table; What is the key? We do not use reserved words for names, we do not use bit flags (that was assembly language. I know this is a skeleton, but he does not have to have broken bones 🙂CREATE TABLE Foobar
(foo_name VARCHAR(15) NOT NULL,
something_seq INTEGER NOT NULL PRIMARY KEY,
something_status SMALLINT NOT NULL,
something_cnt INTEGER NOT NULL);
But this is still wrong. A status is a state of being, so it needs a time period in the form of (start_date, end_date) pairs. This is basic data modeling but I will skip it for now.
T-SQL has had the ANSI syntax for several years; you need to catch up on your MS education:
INSERT INTO Foobar
VALUES ('WAG-3218-UK', 1, 0, 1),
('WAG-3218-UK', 2, 0, 2),
('WAG-3218-UK', 3, 1, 6),
('WAG-3218-UK', 4, 1, 6),
('WAG-3218-UK', 5, 0, 9),
('WAG-3218-UK', 6, 0, 7),
('WAG-3218-UK', 7, 1, 3),
('WAG-3218-UK', 8, 0, 3),
('WAG-3218-UK', 9, 1, 8),
('WAG-3218-UK', 10, 0, 6);
The rest of your post made no sense. Where did this @active_flag come from? How can a BIT be 9? Why are strings being used as integer? SQO is a strongly typed language; data types do not magically change based on flags. We also do not do display formatting in the query.
Try this; pass a NULL to get everything.
CREATE PROCEDURE Fetch_Foobars
(@in_active_flag SMALLINT)
AS
SELECT foo_name, something_seq, something_status, something_cnt
FROM Foobar
WHERE something_status
= COALESCE (@in_active_flag, something_status);
You are not writing SQL yet, just faking your original weakly typed language with SQL.
Is it the wrong time to flame for saying SQO? 😎
May 24, 2012 at 8:31 am
CELKO (5/23/2012)
we do not use bit flags
Until SQL supports a true Boolean data type, bit is the best alternative.
* Why would I reserve 2 bytes for a field that can only have three possible values when I only need a bit to handle those three possible values?
* Why would I use a field that requires a custom check constraint when the bit has a built-in check constraint?
You won't convince people with decrees. You need to demonstrate why it is inferior to some other alternative—and here is the important part—that works in T-SQL 2008.
Your statement that it comes from assembly language doesn't qualify, because it neither provides an alternative nor demonstrates why bit flags are inferior to any other available option.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply