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


how to write expression for Derived Column transform


how to write expression for Derived Column transform

Author
Message
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: 1525 Visits: 1905
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?
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: 18330 Visits: 20414
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.

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: 1525 Visits: 1905
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?
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: 18330 Visits: 20414
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.

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: 1525 Visits: 1905
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
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: 18330 Visits: 20414
Great!


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: 1525 Visits: 1905
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!
lnoland
lnoland
Old Hand
Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)

Group: General Forum Members
Points: 324 Visits: 821
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
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: 1525 Visits: 1905
Hmmm, certainly looks promising, but I don't see COALESCE() available in SSIS transforms. Do you?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85469 Visits: 41081
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
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