Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Derived Column to handled mulitple conditions in CASE statement? Expand / Collapse
Author
Message
Posted Friday, May 24, 2013 3:20 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 10:28 AM
Points: 703, Visits: 1,651
HI, I've got a case statement that works, but because I have to do it in SSIS I am at a loss:

---this works
, CASE VW.LT
when 'B1' then 'STD'
when 'B7' then 'Q2FC'
when 'B8' then 'Q3FC'
when 'B9' then 'Q4FC'
end as ValueType

As you can see, the case statement evaluates values in one column, and depending on what they are, renames them.

I've pulled out the Derived Column transform to accomplish but unable to find a way to handle more than one condition.

---this works
([Copy of LT] =="B9")?"STD": "NonSTD"

--but this doesn't work
([Copy of LT] =="B9")?"STD": ( [Copy of LT] == "B7"?"Q2FC" [Copy of LT]=="B8"?"Q3FC" [Copy of LT]=="B9"?"Q4FC")

I need to handle all conditions. How to do it in SSIS?
Post #1456697
Posted Friday, May 24, 2013 7:05 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, June 9, 2014 6:15 AM
Points: 79, Visits: 243
In Derived column you use following expression:

(@[User::CopyofLT] == "B1") ? "STD" : ((@[User::CopyofLT] == "B7") ? "Q2FC" : ((@[User::CopyofLT] == "B8") ? "Q3FC" : ((@[User::CopyofLT] == "B9") ? "Q4FC" : "NonSTD")))

I have used a variable to test the expression, you can replace it with your column name. This is working as you described for all cases.


Vikash Kumar Singh || www.singhvikash.in
Post #1456747
Posted Saturday, May 25, 2013 11:25 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 10:28 AM
Points: 703, Visits: 1,651
Vikash, thanks a lot.
I put it in that way and Derived Column Transformation Editor 'accepted' it, meaning the Expression went black. I haven't run the SSIS package yet, but looks good so far.
Happy Memorial Day weekend
Post #1456842
Posted Sunday, May 26, 2013 11:14 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 10:28 AM
Points: 703, Visits: 1,651
I have another CASE statement, similar but different, with which I am struggling.

Here's the case statement:
, AcctNum = CASE
When [Obj Acct] = 0 then [Sub]
When [Sub] = 0 then [Obj Acct]
Else [Obj Acct] + '.' + [Sub]
End

FYI
[Obj Acct] is nvarchar
[Sub] is nvarchar


Because now I am referring to column headings, and not values, eg. [Obj Acct] and [Sub], I think I need slightly different syntax, because the column names don't belong within quotes, but I can't get the transform to accept the way I did it (see below, remains red).

([Copy of Obj Acct]=="0")? [Copy of Sub] ([Copy of Sub]=="0")?[Copy of Obj Acct]: [Copy of Obj Acct]+"."+ [Copy of Sub]))

Can you help me convert this second CASE statement into a Derived Column transform expression? Thx

Thanks.
Post #1456877
Posted Sunday, May 26, 2013 12:58 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, June 9, 2014 6:15 AM
Points: 79, Visits: 243
You can use below expression in the derived column.

[Obj Acct] == "0" ? Sub : (Sub == "0" ? [Obj Acct] : [Obj Acct] + "." + Sub)


Vikash Kumar Singh || www.singhvikash.in
Post #1456903
Posted Sunday, May 26, 2013 2:35 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 10:28 AM
Points: 703, Visits: 1,651
awesome. Thanks. I will learn this thanks to your showing me the way. I appreciate it.
Post #1456919
Posted Monday, May 27, 2013 7:49 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 10:28 AM
Points: 703, Visits: 1,651
Vikash,

I would like the expression to evaluate without quotes around zero:

[Obj Acct] == 0? Sub : (Sub == 0? [Obj Acct] : [Obj Acct] + "." + Sub)[/

so that it works the way my case statement works:
, AcctNum = CASE
When [Obj Acct] = 0 then [Sub]
When [Sub] = 0 then [Obj Acct]
Else [Obj Acct] + '.' + [Sub]
End

With quotes the expression parses in SSIS but column values are returning with periods even when there is no value in the Sub or [Obj Acct] columns. EG. 55515. or .111. To be honest, I don't know why 0 works, because those columns don't actually have a 0 in them. They are just blank.
However, without quotes the expression won't parse. Do you know what I can do?
Post #1457169
Posted Tuesday, May 28, 2013 3:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:45 AM
Points: 5,254, Visits: 12,178
The syntax for the derived column gets difficult to maintain doing it this way, as more lookups are added.

An alternative is to use a lookup table - either physical or virtual (ie, generated at run time) - and add a suitable lookup to your data flow.

The benefit of using a physical table is that you can add more lookups without having to modify the design of your package.



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

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1457235
Posted Tuesday, May 28, 2013 9:35 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 10:28 AM
Points: 703, Visits: 1,651
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.
Post #1457407
Posted Tuesday, May 28, 2013 2:40 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 10:28 AM
Points: 703, Visits: 1,651
Sought advice from colleague.

This worked (double quotes encasing a space)


[Copy of Obj Acct] == " " ? [Copy of Sub] : ([Copy of Sub] == " " ? [Copy of Obj Acct] : [Copy of Obj Acct] + "." + [Copy of Sub])




This didn't (double quotes encasing no space)


[Copy of Obj Acct] == "" ? [Copy of Sub] : ([Copy of Sub] == "" ? [Copy of Obj Acct] : [Copy of Obj Acct] + "." + [Copy of Sub])




Now the concatenation is working as intended, with no period if either [Sub] or [Obj Acct] column is empty.
--I will need to evaluate why the excel column [Sub], when containing nothing is importing to SSIS as a space....
Post #1457518
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse