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»»

how to write expression for Derived Column transform Expand / Collapse
Author
Message
Posted Wednesday, May 29, 2013 1:59 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: 2 days ago @ 8:10 AM
Points: 656, Visits: 1,533
I need to filter out all records as follows:

where
[Month 01] is null and
[Month 02] is null and
[Month 03] is null and
[Month 04] is null and
[Month 05] is null and
[Month 06] is null and
[Month 07] is null and
[Month 08] is null and
[Month 09] is null and
[Month 10] is null and
[Month 11] is null and
[Month 12] is null

The above is t-sql. I want to do in Derived Column. Tried the following but it's not parsing:

[Month 01] !=NULL &&[Month 02] !=NULL] && etc.


How to do the same in SSIS in Derived Column transform?
Post #1457953
Posted Thursday, May 30, 2013 1:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, September 15, 2014 7:23 AM
Points: 5,074, Visits: 11,852
Maybe try something like

Isnull([Month 01]) && Isnull([Month 02]) etc



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 #1458068
Posted Thursday, May 30, 2013 4:45 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: 2 days ago @ 8:10 AM
Points: 656, Visits: 1,533
Phil, turns out that Derived Column is not for such kind of work because I am needing to filter rows and not handle them ie. concatenate, rename, test a condition, ie derive.


need to find a transformation which do the equivalent work of a WHERE clause...to remove rows from dataset where certain columns evaluate to NULL.

Does anyone think SORT will work?
Post #1458120
Posted Thursday, May 30, 2013 6:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, September 15, 2014 7:23 AM
Points: 5,074, Visits: 11,852
Conditional Split is the task you need. Add it after the derived column from the previous post. Send all NULLs to one output and everything else to the other. Then use only the 'everything else' output.


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 #1458178
Posted Thursday, May 30, 2013 6:31 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: 2 days ago @ 8:10 AM
Points: 656, Visits: 1,533
That worked. Thanks Phil. I was trying to accomplish before unpivot.
In my dataset I sometimes have rows, where all 12 of the 12 rows that I am unpivoting (using unpivot transform) contain a NULL. I wanted to filter those out before the unpivot.

However, if I use conditional split after the unpivot, on the aggregated column, it excludes those rows I'm after. So for now I am fine


Still.....if I weren't unpivoting then I wouldn't have a solution. I wonder....how would I do the equivalent of this?
where
[Month 01] is null and
[Month 02] is null and
[Month 03] is null and
[Month 04] is null and
[Month 05] is null and
[Month 06] is null and
[Month 07] is null and
[Month 08] is null and
[Month 09] is null and
[Month 10] is null and
[Month 11] is null and
[Month 12] is null
Post #1458186
Posted Thursday, May 30, 2013 6:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, September 15, 2014 7:23 AM
Points: 5,074, Visits: 11,852
Great!


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 #1458188
Posted Thursday, May 30, 2013 6:41 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: 2 days ago @ 8:10 AM
Points: 656, Visits: 1,533
Phil, I modified my post while you were answering. I'm not placing the burden of this question on you, but I decided to push it a little further (if you care to look ) No hurry!
Post #1458192
Posted Friday, May 31, 2013 9:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 21, 2014 3:31 PM
Points: 114, Visits: 548
KoldCoffee (5/30/2013)

Still.....if I weren't unpivoting then I wouldn't have a solution. I wonder....how would I do the equivalent of this?
where
[Month 01] is null and
[Month 02] is null and
[Month 03] is null and
[Month 04] is null and
[Month 05] is null and
[Month 06] is null and
[Month 07] is null and
[Month 08] is null and
[Month 09] is null and
[Month 10] is null and
[Month 11] is null and
[Month 12] is null


How about:

WHERE COALESCE([Month 01], [Month 02], [Month 03], [Month 04]
, [Month 05], [Month 06], [Month 07], [Month 08], [Month 09]
, [Month 10], [Month 11], [Month 12]) IS NOT NULL



- Les
Post #1458762
Posted Saturday, June 1, 2013 11:38 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: 2 days ago @ 8:10 AM
Points: 656, Visits: 1,533
Hmmm, certainly looks promising, but I don't see COALESCE() available in SSIS transforms. Do you?
Post #1458983
Posted Sunday, June 2, 2013 9:27 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:16 PM
Points: 37,102, Visits: 31,655
KoldCoffee (5/29/2013)
I need to filter out all records as follows:

where
[Month 01] is null and
[Month 02] is null and
[Month 03] is null and
[Month 04] is null and
[Month 05] is null and
[Month 06] is null and
[Month 07] is null and
[Month 08] is null and
[Month 09] is null and
[Month 10] is null and
[Month 11] is null and
[Month 12] is null

The above is t-sql. I want to do in Derived Column. Tried the following but it's not parsing:

[Month 01] !=NULL &&[Month 02] !=NULL] && etc.


How to do the same in SSIS in Derived Column transform?


Just a thought... if the data were normalized instead of pivoted, you wouldn't even have the NULLs to deal with.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1459059
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse