Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Case Statement Help

Author  Topic 

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-03-01 : 06:55:10
Hey Guys

I need some help with a case statment

This is my query

SELECT FDMSAccountNo,
SUBSTRING(Comments2, 39, 3) AS [Sub Route]
FROM stg_FDMS_Merchant_Membership_Data

This query so far produces two columns (FDMSAccountNo & Sub Route)

I would like to produce another colyum called "[Assigned to]"

I wrote the following case statement (but i belive thats it incorrect)
case when SUBSTRING(Comments2, 39, 3) = 'E' then 'Base24 main' else 0 end [Assigned to]


Below is a list of every combination sub route, and assigned to can be

Hoping you can help


Sub Route Assigned to
A Paper imput/ Traditional data entry
B Credit Call
C YesPay
D Direct Sends
E Base24 main / pilot
F Payoffshore.com M/C
G SixCard
H TNS
I
J
K FIS
L Centre file
M ITS
N Servebase
O Adflex
P Realex
Q NetBanx
R WorldPay
S
T SagePay
U Secure Trading
V Cybersource
W Commidea
X UPG
Y BT Buynet
Z Capita
1 Integral
2 Xenco
3
4 TLG
5 Anderson Zaks
6
7 Paypoint
8 Fintrax
9 Datacash

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-01 : 07:00:00
you need to create a mapping atble with above information and then do join with it based on substring logic to get your output

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-03-01 : 07:01:56
Hi visakh16

how do you do that ?
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-01 : 07:07:20
May be this?

SELECT FDMSAccountNo,
SUBSTRING(Comments2, 39, 3) AS [Sub Route],
CASE WHEN SUBSTRING(Comments2, 39, 3) = 'E' THEN 'Base24 main'
ELSE '0' END [Assigned To]

FROM stg_FDMS_Merchant_Membership_Data


--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-01 : 07:08:14
1.create a new table using CREATE TABLE syntax with two columns SubRoute, Assignedto
2. insert above data to table
3. make your query like

SELECT t.*,t1.Assignedto
FROM
(
SELECT FDMSAccountNo,
SUBSTRING(Comments2, 39, 3) AS [Sub Route]
FROM stg_FDMS_Merchant_Membership_Data
)t
JOIN YourNewCreatedTable t1
On t1.SubRoute = t.[Sub Route]


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-01 : 07:09:25
quote:
Originally posted by bandi

May be this?

SELECT FDMSAccountNo,
SUBSTRING(Comments2, 39, 3) AS [Sub Route],
CASE WHEN SUBSTRING(Comments2, 39, 3) = 'E' THEN 'Base24 main'
ELSE '0' END [Assigned To]

FROM stg_FDMS_Merchant_Membership_Data


--
Chandu


what about the others values in the list? will require about 40 case whens if using this approach

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-03-01 : 07:12:56
Bandi - i tried your method, and regardless of the Sub route letter it produces 0 in the [Assigned To] column

Visakh16 - i dont have access rights to create tables in the database :(

Would it be possible to do a right or left instead of a substring ?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-01 : 07:17:22
quote:
Originally posted by masond

Bandi - i tried your method, and regardless of the Sub route letter it produces 0 in the [Assigned To] column

Visakh16 - i dont have access rights to create tables in the database :(


Would it be possible to do a right or left instead of a substring ?



create a derived table or table variable then like below


SELECT t.*,t1.Assignedto
FROM
(
SELECT FDMSAccountNo,
SUBSTRING(Comments2, 39, 3) AS [Sub Route]
FROM stg_FDMS_Merchant_Membership_Data
)t
JOIN (
SELECT CAST('A' AS char(1)) AS SubRoute,
CAST('Paper imput/ Traditional data entry' AS varchar(1000)) AS AssignedTo
UNION ALL
SELECT 'B','Credit Call
UNION ALL
SELECT 'C','YesPay'
UNION ALL
...
)t1
On t1.SubRoute = t.[Sub Route]



put all values inside derived table t1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-01 : 07:19:45
quote:
Originally posted by masond

Bandi - i tried your method, and regardless of the Sub route letter it produces 0 in the [Assigned To] column
Visakh16 - i dont have access rights to create tables in the database :(
Would it be possible to do a right or left instead of a substring ?


Means if [Sub Route] is not equal to 'E' then what is the output?

EDIT:
I got your point now... If that is the case, where do you have those combinations in databases?
--
Chandu
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-03-01 : 07:40:56
HEY BANDI AND CO

i HAVE DONE THE FOLLOWING

SELECT FDMSAccountNo,
SUBSTRING(Comments2, 39, 3) AS [Sub Route],
CASE
WHEN LEN(Comments2) = 41 and right(Comments2,3) like '%E%' THEN 'Base'
WHEN LEN(Comments2) = 41 and right(Comments2,3) like '%T%' THEN 'Base2'
end as SR
FROM stg_FDMS_Merchant_Membership_Data
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-01 : 08:12:09
quote:
Originally posted by masond

HEY BANDI AND CO

i HAVE DONE THE FOLLOWING

SELECT FDMSAccountNo,
SUBSTRING(Comments2, 39, 3) AS [Sub Route],
CASE
WHEN LEN(Comments2) = 41 and right(Comments2,3) like '%E%' THEN 'Base'
WHEN LEN(Comments2) = 41 and right(Comments2,3) like '%T%' THEN 'Base2'
end as SR
FROM stg_FDMS_Merchant_Membership_Data


see my latest suggestion

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-03-01 : 08:30:57
hi visakh16

i get the following

Msg 102, Level 15, State 1, Line 14
Incorrect syntax near ','.
Msg 105, Level 15, State 1, Line 14
Unclosed quotation mark after the character string '
UNION ALL
...
)t1
On t1.SubRoute = t.[Sub Route]
'.
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-01 : 08:34:25
Hi Masond,

You have to include remaining combinations there after UNION ALL

UNION ALL
...
)t1


--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-01 : 08:52:29
quote:
Originally posted by masond

hi visakh16

i get the following

Msg 102, Level 15, State 1, Line 14
Incorrect syntax near ','.
Msg 105, Level 15, State 1, Line 14
Unclosed quotation mark after the character string '
UNION ALL
...
)t1
On t1.SubRoute = t.[Sub Route]
'.



you're not doing it properly

show your code
i hope you replaced ... with other values you had in list

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -