SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Case Statement Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masond
Constraint Violating Yak Guru

447 Posts

Posted - 03/01/2013 :  06:55:10  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 03/01/2013 :  07:00:00  Show Profile  Reply with Quote
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 - 03/01/2013 :  07:01:56  Show Profile  Reply with Quote
Hi visakh16

how do you do that ?
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 03/01/2013 :  07:07:20  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 03/01/2013 :  07:08:14  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 03/01/2013 :  07:09:25  Show Profile  Reply with Quote
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 - 03/01/2013 :  07:12:56  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 03/01/2013 :  07:17:22  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 03/01/2013 :  07:19:45  Show Profile  Reply with Quote
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

Edited by - bandi on 03/01/2013 07:24:04
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 03/01/2013 :  07:40:56  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 03/01/2013 :  08:12:09  Show Profile  Reply with Quote
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 - 03/01/2013 :  08:30:57  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 03/01/2013 :  08:34:25  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 03/01/2013 :  08:52:29  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000