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_DataThis 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 beHoping you can help Sub Route Assigned toA Paper imput/ Traditional data entryB Credit CallC YesPayD Direct Sends E Base24 main / pilot F Payoffshore.com M/CG SixCardH TNSI J K FISL Centre fileM ITSN ServebaseO AdflexP RealexQ NetBanxR WorldPayS T SagePayU Secure Trading V CybersourceW CommideaX UPGY BT BuynetZ Capita1 Integral2 Xenco3 4 TLG5 Anderson Zaks6 7 Paypoint8 Fintrax9 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 MVPhttp://visakhm.blogspot.com/ |
|
|
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2013-03-01 : 07:01:56
|
Hi visakh16how do you do that ? |
|
|
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 |
|
|
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, Assignedto2. insert above data to table3. make your query likeSELECT t.*,t1.AssignedtoFROM(SELECT FDMSAccountNo, SUBSTRING(Comments2, 39, 3) AS [Sub Route]FROM stg_FDMS_Merchant_Membership_Data)tJOIN YourNewCreatedTable t1On t1.SubRoute = t.[Sub Route] ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
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 ? |
|
|
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 belowSELECT t.*,t1.AssignedtoFROM(SELECT FDMSAccountNo, SUBSTRING(Comments2, 39, 3) AS [Sub Route]FROM stg_FDMS_Merchant_Membership_Data)tJOIN (SELECT CAST('A' AS char(1)) AS SubRoute,CAST('Paper imput/ Traditional data entry' AS varchar(1000)) AS AssignedToUNION ALLSELECT 'B','Credit CallUNION ALLSELECT 'C','YesPay'UNION ALL...)t1On t1.SubRoute = t.[Sub Route] put all values inside derived table t1------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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 |
|
|
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],CASEWHEN 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 SRFROM stg_FDMS_Merchant_Membership_Data |
|
|
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],CASEWHEN 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 SRFROM stg_FDMS_Merchant_Membership_Data
see my latest suggestion------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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 14Incorrect syntax near ','.Msg 105, Level 15, State 1, Line 14Unclosed quotation mark after the character string 'UNION ALL...)t1On t1.SubRoute = t.[Sub Route]'. |
|
|
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 ALLUNION ALL...)t1--Chandu |
|
|
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 14Incorrect syntax near ','.Msg 105, Level 15, State 1, Line 14Unclosed quotation mark after the character string 'UNION ALL...)t1On t1.SubRoute = t.[Sub Route]'.
you're not doing it properlyshow your codei hope you replaced ... with other values you had in list------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|