Derived Shape - Replacing a column with itself.

Hi there,

I have a derived column shape in which I replace a column with an expression.

The expression is an IF statement - a true result sets a value for the column and a false result just uses the existing value of the column (ie it replaces it with itself)

Like this.

ColumnName DerivedColumn Expression

ColumnA Replace 'ColumnA' ColumnB != ColumnC ? "y" : ColumnA

But whenever, the condition is NOT met, ColumnA is set to NULL!!!!

Does this mean that the column value is deleted before the Expression is applied?

If this is how it is meant to work, then does anyone have a way of doing what I want to do without creating extra columns in the dataset?

Cheers.

I'm wondering if there are some data typing issues here. Wha is the type of ColumnA?

-Jamie

|||

Hi Jamie,

It's just a unicode string.

Dave.

|||

I mean in the pipeline. Is it a DT_WSTR?

if so, ensure you don't have any implicit conversions going on. i.e. explicitly cast "Y" as a DT_WSTR.

Also, first try to get this working as a new column rather than replacing ColumnA. See if you exhibit the same problems in that scenario.

-Jamie

|||

Jamie,

in my example setting Column A to the value "y" works fine. It's setting Column A to itself (ie II just want it to retain it's original value) which is the problem.

Do you suggest I explicitly cast the ColumnA in the expression? So with my example....

ColumnName DerivedColumn Expression

ColumnA Replace 'ColumnA' ColumnB != ColumnC ? "y" : CAST(ColumnA...)

|||

Dave,

No, that's not quite what i meant.

Try this:

DerivedColumn Expression

Replace 'ColumnA' ColumnB != ColumnC ? (DT_WSTR)"y" : ColumnA

My second suggestion was to see if this worked first:

DerivedColumn Expression

Add as new column ColumnB != ColumnC ? "y" : ColumnA

I'm clutching at straws a little bit but if I were you I would definately try to recreate the problem by adding it as a new column rather than replacing ColumnA.

-Jamie

|||

Hi Jamie,

adding a column works fine. It's replacing an exising column that has the "problem".

I should point out as well that the issue only arises when you use an IF condition in the expression.

So this is OK

DerivedColumn Expression

Replace ColumnA ColumnA+"Hello"

This will replace ColumnA with what was in ColumnA + "Hello"

This is NOT OK

DerivedColumn Expression

Replace ColumnA ColumnB != ColumnC ? "y" : ColumnA

So if ColumnB = ColumnC, then ColumnA is set to NULL - NOT what ColumnA was before the expression was applied.

I reckon its a bug.

|||

Hi Dave,

Can you post a simple repro that doesn't reply on external data sources (i.e. just create the same data using a script source component) and then post it up here?

To post up here, just copy the contents of your .dtsx file into your reply.

Thanks

Jamie

|||

Sure,

I'll do it tomorrow.

Speak then.

|||Have you checked if Column B or C is NULL? If so you will always get NULL out. Example here http://wiki.sqlis.com/default.aspx/SQLISWiki/Expressions.html|||

You can quickly test for what Darren is saying by changing your expression like this:

false ? "y" : ColumnA

If ColumnB or ColumnC are NULL, and you want to fail the comparison in that case,you can make your expression like this:

!ISNULL(ColumnB) && !ISNULL(ColumnC) && ColumnB != ColumnC ? "y" : ColumnA

If you want the condition to do NULL comparisions (that is, you want ColumnB != ColumnC to return true if one is NULL and the other is not, you might need something more like:

(ISNULL(ColumnB) != ISNULL(ColumnC)) || !ISNULL(ColumnB) && !ISNULL(ColumnC) && ColumnB != ColumnC ? "y" : ColumnA

|||

Your right Darren - it was the NULL values. aaargh!!!

Thanks for everyone's input.