Customizing case statement (Network Steve Forum)

Customizing case statement

CREATE TABLE Test
 (
 EDate Datetime,
 Code varchar(255),
 Cdate int,
 Price int
 ); 
 

 
 
 insert into Test
 values('2015-06-19','CL',20150701,12)
  insert into Test
 values('2015-06-22','CL',20150701,26)
  insert into Test
 values('2015-06-23','CL',20150701,73)
  insert into Test
 values('2011-04-08','CL',20110501,97)
  insert into Test
 values('2011-04-07','CL',20110501,11)
  insert into Test
 values('2011-04-06','CL',20110501,61)

  insert into Test
 values('2011-04-08','XP',20110501,37)
  insert into Test
 values('2011-04-07','XP',20110501,19)
  insert into Test
 values('2011-04-06','XP',20110501,25)
 
  insert into Test
 values('2015-06-19','HO',20150701,92)
  insert into Test
 values('2015-06-22','HO',20150701,43)
  insert into Test
 values('2015-06-23','HO',20150701,22)

Please refer to the below query. Suppose tomorrow if i decide that Code in('RXP','RXC')statement is not required then i want to remove it from the output. or if i think we need to add another Code like Code in('OHP','OHC') and New Code like 'HO',we want to add it to the output. Is there any anyway to add or delete case statement and change where clause dynamically.

select

[EDate]

,[Code]

,[Cdate]

,[Price],

case when Code in('LOP','LOC') then 'CL'

when Code in('OBP','OBC') then 'RB'

when Code in('RXP','RXC') then 'RX'

else 'NocodeFound' end Code

from test

where Code in('LOP','LOC','OBP','OBC','RXP','RXC')

Changed type Jingyang LiMicrosoft community contributor, Moderator 12 hours 46 minutes ago

July 9th, 2015 2:18pm

Try like this from next time onward you don't require to change any thing in your query while excluding any of the code . (need to run an update statement only).

While adding a new code you need to add the same in your script .but for exclusion no comment/delete in your code

CREATE TABLE Test_2
(
EDate Datetime,
Code varchar(255),
Cdate int,
Price int,
Exclusion_Status int
);

insert into Test_2
values('2015-06-19','LOP',20150701,12,1)
insert into Test_2
values('2015-06-22','LOP',20150701,26,1)
insert into Test_2
values('2015-06-23','LOC',20150701,73,1)
insert into Test_2
values('2011-04-08','LOC',20110501,97,1)
insert into Test_2
values('2011-04-07','LOC',20110501,11,1)
insert into Test_2
values('2011-04-06','OBP',20110501,61,1)
insert into Test_2
values('2011-04-08','OBP',20110501,37,1)
insert into Test_2
values('2011-04-07','OBC',20110501,19,1)
insert into Test_2
values('2011-04-06','OBC',20110501,25,1)
insert into Test_2
values('2015-06-19','RXP',20150701,92,1)
insert into Test_2
values('2015-06-22','RXP',20150701,43,1)
insert into Test_2
values('2015-06-23','RXC',20150701,22,1)

select [EDate] ,[Code],[Cdate] ,[Price],
case when Code in('LOP','LOC' ) then 'CL'
when Code in('OBP','OBC') then 'RB'
when Code in('RXP','RXC') then 'RX'
else 'NocodeFound' end Code
from Test_2
where Code in('LOP','LOC','OBP','OBC','RXP','RXC' )
and Exclusion_Status = 0

UPDATE Test_2 SET Exclusion_Status=1 where Code in('LOP','LOC')

Hope this will help you .

Free Windows Admin Tool Kit Click here and download it now

July 9th, 2015 2:41pm

Hi SyedF!

This can absolutely be done with Dynamic SQL using sp_executesql; however, I feel it's better to build a code table and join it like the following:

Declare @Test_2 TABLE
(	EDate Datetime,
	Code varchar(255),
	Cdate int,
	Price int	); 

Declare @Codes Table
(	Code varchar(255), 
	CodeLabel varchar(255)	);
  
insert into @Test_2 values('2015-06-19','LOP',20150701,12)
insert into @Test_2 values('2015-06-22','LOP',20150701,26)
insert into @Test_2 values('2015-06-23','LOC',20150701,73)
insert into @Test_2 values('2011-04-08','LOC',20110501,97)
insert into @Test_2 values('2011-04-07','LOC',20110501,11)
insert into @Test_2 values('2011-04-06','OBP',20110501,61)
insert into @Test_2 values('2011-04-08','OBP',20110501,37)
insert into @Test_2 values('2011-04-07','OBC',20110501,19)
insert into @Test_2 values('2011-04-06','OBC',20110501,25) 
insert into @Test_2 values('2015-06-19','RXP',20150701,92)
insert into @Test_2 values('2015-06-22','RXP',20150701,43)
insert into @Test_2 values('2015-06-23','RXC',20150701,22)
insert into @Test_2 values('2015-06-23','OHP',20150701,21)
insert into @Test_2 values('2015-06-23','OHC',20150701,11)
insert into @Test_2 values('2015-06-23','QQQ',20150701,99)

insert into @Codes values( 'LOP', 'CL' )
insert into @Codes values( 'LOC', 'CL' )
insert into @Codes values( 'OBP', 'RB' )
insert into @Codes values( 'OBC', 'RB' )
insert into @Codes values( 'RXP', 'RX' )
insert into @Codes values( 'RXC', 'RX' )
insert into @Codes values( 'RXC', 'RX' )
insert into @Codes values( 'OHP', 'OH' )
insert into @Codes values( 'OHC', 'OH' )


SELECT EDate ,t.Code, Cdate, Price, isnull(c.CodeLabel, 'NocodeFound') as CodeLabel
FROM @Test_2 t
LEFT JOIN @Codes c on (c.Code = t.Code)
WHERE t.Code in('LOP','LOC','OBP','OBC','RXP','RXC', 'OHP', 'OHC', 'QQQ'

One of the reasons I would go this route is that the code is easily reused. Think about what a pain it will be if you have to cut-n-paste the code translation to other procs!

I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)

July 9th, 2015 3:21pm

Your DDL is so wrong. You have no key. Your data types are over-sized and erroneous! A date is never an integer. There is no non decimal currency on Earth for a price.

What you tried to post is a silly as asking Oon a scale from 10 to 10 what color is your favorite letter of the alphabet?

Is this what you were trying to say?

CREATE TABLE Tests
(test_date DATE NOT NULL,
foobar_code CHAR(2) NOT NULL
CHECK (foobar_code IN ('CL', XP', 'HO')),
PRIMARY KEY (test_date, foobar_code),
foobar_date DATE NOT NULL,
test_price DECIMAL (8,2) NOT NULL
CHECK (test_price >= 0.00)
);

INSERT INTO Tests
VALUES
('2011-04-06', 'CL', '2011-05-01', 61.00),
('2011-04-06', 'XP', '2011-05-01', 25.00),
('2011-04-07', 'CL', '2011-05-01', 11.00),
('2011-04-07', 'XP', '2011-05-01', 19.00),
('2011-04-08', 'CL', '2011-05-01', 97.00),
('2011-04-08', 'XP', '2011-05-01', 37.00),
('2015-06-19', 'CL', '2015-07-01', 12.00),
('2015-06-19', 'HO', '2015-07-01', 92.00),
('2015-06-22', 'CL', '2015-07-01', 26.00),
('2015-06-22', 'HO', '2015-07-01', 43.00),
('2015-06-23', 'CL', '2015-07-01', 73.00),
('2015-06-23', 'HO', '2015-07-01', 22.00);

>> Please refer to the below query. Suppose tomorrow if I decide that foobar_code in('RXP', 'RXC')statement is not required THEN I want to remove it from the output. or if I think we need to add another foobar_code like foobar_code in ('OHP', 'OHC') and New foobar_code like 'HO', we want to add it to the output. Is there any anyway to add or delete CASE statement [sic: CASE is an expression, not statement] and change where clause dynamically. <<

Look at the ELSE clause of your CASE expression and think about it.

SELECT test_date, foobar_code, foobar_date, test_price ,
CASE WHEN foobar_code IN ('LOP', 'LOC') THEN 'CL'
WHEN foobar_code IN ('OBP', 'OBC') THEN 'RB'
WHEN foobar_code IN('RXP', 'RXC') THEN 'RX'
ELSE 'NocodeFound' END AS foobar_type
FROM Tests;

Do these encodings change so much or so often that you need to dynamically change this query? Really? I would re-write the query as needed.

But is that is true, then you can use a look-up table:

CREATE TABLE Foobar_Lookups
(foobar_code CHAR(3) NOT NULL PRIMARY KEY,
foobar_type CHAR(2) NOT NULL);

Free Windows Admin Tool Kit Click here and download it now

July 9th, 2015 7:48pm

This topic is archived. No further replies will be accepted.