| Author |
 Topic  |
|
|
masond
Posting Yak Master
241 Posts |
 Posted - 03/01/2013 : 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
India
47023 Posts |
Posted - 03/01/2013 : 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/
|
 |
|
|
masond
Posting Yak Master
241 Posts |
Posted - 03/01/2013 : 07:01:56
|
Hi visakh16
how do you do that ? |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 03/01/2013 : 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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 03/01/2013 : 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/
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 03/01/2013 : 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/
|
|
|
|
masond
Posting Yak Master
241 Posts |
Posted - 03/01/2013 : 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 ? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 03/01/2013 : 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/
|
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 03/01/2013 : 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 |
Edited by - bandi on 03/01/2013 07:24:04 |
|
|
|
masond
Posting Yak Master
241 Posts |
Posted - 03/01/2013 : 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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 03/01/2013 : 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/
|
|
|
|
masond
Posting Yak Master
241 Posts |
Posted - 03/01/2013 : 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]
'. |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 03/01/2013 : 08:34:25
|
Hi Masond,
You have to include remaining combinations there after UNION ALL
UNION ALL
...
)t1
--
Chandu |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 03/01/2013 : 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/
|
|
|
| |
Topic |
|
Case Statement Help
