SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Derived Column to handled mulitple conditions in CASE statement?


Derived Column to handled mulitple conditions in CASE statement?

Author
Message
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18177 Visits: 20392
KoldCoffee (5/28/2013)
Phil, I don't get what you're saying. How would a Lookup table help me in a situation where I need to derive new values in the source table based on existing values (also in the source table)? Have you done this? If so, please provide some example.
I have successfully implemented lookups for retrieving additional columns from referenced dataset columns (for eg. to handle joins) but what I need in the above scenario is the syntax to handle for a case statement.
The syntax Vikash gave me works....except that it needs to evaluate 0 without quotes.


I'm sorry about that - my response was aimed at your first post, not the subsequent one, which I had assumed was a similar question, without reading it closely enough. You are correct, my idea cannot be applied in this case, though it would work for your first post.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
KoldCoffee
KoldCoffee
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1519 Visits: 1905
Phil, you've always been spot on for me in the past, so I was w00t and Ermm and Crazy. and a little bit wondering if it had more to do with other post too, after I about half a day of it teasing the outer fringes of my brain :-)

Both problems solved. Lookups turns out to bring forward all the inputs plus the referenced columns and thank goodness for all the help and materials on line and here.
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18177 Visits: 20392
KoldCoffee (5/28/2013)
Phil, you've always been spot on for me in the past, so I was w00t and Ermm and Crazy. and a little bit wondering if it had more to do with other post too, after I about half a day of it teasing the outer fringes of my brain :-)

Both problems solved. Lookups turns out to bring forward all the inputs plus the referenced columns and thank goodness for all the help and materials on line and here.


Well done on getting it solved!


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
KoldCoffee
KoldCoffee
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1519 Visits: 1905
Vikash, Phil, somebody?

[Business Unit] is MFMGOOGLE and I want just the 'GOOGLE' part.

This parses in the Derived Column transformation but isn't working as intended:

SUBSTRING([Business Unit],4,LEN([Business Unit]) - 3)


How to fix this expression to return intended result?
Vikash Kumar Singh
Vikash Kumar Singh
SSC-Enthusiastic
SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)

Group: General Forum Members
Points: 110 Visits: 243
You can use following:

RIGHT([Business Unit],6)

Vikash Kumar Singh || www.singhvikash.in
KoldCoffee
KoldCoffee
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1519 Visits: 1905
Vikash, could you kindly help me with this string here http://www.sqlservercentral.com/Forums/Topic1458631-364-2.aspx#bm1459386

I can't help myself and no one else can either, so far. I think you will know answer.
Andre 425568
Andre 425568
SSC Veteran
SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)

Group: General Forum Members
Points: 217 Visits: 187
Hi

I have something very similar but there are nulls involved, not sure how this will affect the script.

The field want to use derived column is a field with say 12 characters.

Field1 - data is for example
123456abcdef
123456
null

The case statement I want to use is
CASE WHEN FIELD1 IS NULL
THEN NULL
WHEN SUBSTRING(FIELD1 , 7, 6) IS NULL
THEN ' '
ELSE RTRIM(SUBSTRING(FIELD1 , 7, 6))
END as FIELD2

Running this Case statement would give results
'abcdef'
''
null

How would I add then as a derived column.

Then example 2 is tougher

CASE WHEN FIELD1 IS NULL THEN NULL
WHEN SUBSTRING(REPLICATE('0', 6 - LEN(FIELD1)) + CAST(FIELD1 AS VARCHAR(6)), 7, 6) IS NULL
THEN 0
ELSE RTRIM(SUBSTRING(REPLICATE('0', 6 - LEN(FIELD1)) + CAST(FIELD1 AS VARCHAR(6)), 7, 6))
END

My third question is would it be faster running this as a sql statement to import rows, or would a derived column with a fastload on the destination be faster. We are in this case talking about a few hundred million rows.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search