derived column syntax

Question

derived column syntax

rui_fro

SSC-Addicted

Points: 467
More actions
May 8, 2008 at 9:56 am

#207132

Hi!
Can anyone help me with the syntax of this expression:
((OutputSalesHeaderStatus == 1) ? TRUE : FALSE )
It doesn't catch the FALSE statement.
Maybe some brakets are missing or something else...
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Nick Maroudas SSC Eights! Points: 977 More actions · Answer 1

Are you using SSIS here? Assuming you are then your expression should be a boolean expression so something like ISNULL([expression]) ? 'TRUE' : 'FALSE'

rui_fro SSC-Addicted Points: 467 More actions · Answer 2

Yes, I'm using SSIS.

OutputSalesHeaderStatus(type:integer) is an output of a lookup. If that output is 1, then a field in another table is updated. It cannot be null.

Is there other way?

Thanks for the reply

Nick Maroudas SSC Eights! Points: 977 More actions · Answer 3

rui_fro (5/8/2008)
Yes, I'm using SSIS.
OutputSalesHeaderStatus(type:integer) is an output of a lookup. If that output is 1, then a field in another table is updated. It cannot be null.
Is there other way?
Thanks for the reply

Ok, it's clearer now, when you are done using your lookup add the conditional split object, in the conditions you can add OutputSalesHeaderStatus== 1 as your first output and then OutputSalesHeaderStatus ==0 or whatever you want in for the second output, then you can do what you need following on to those rows that are = 1 and to the rows <> 0 in another stream. Then you can union the 2 x datasets together. I hope this helps, i have made assuptions on what happens after you have determined 1 or 0 (true or false)

rui_fro SSC-Addicted Points: 467 More actions · Answer 4

Hi!

After de lookup, I made a conditional split with the condition:

(OutputSalesHeaderStatus == 1)

Then a derived column with the expression:

(ISNULL(OutputSalesHeaderStatus) ? FALSE : TRUE )

I have the following error:

The expression "(OutputSalesHeaderStatus == 1)" on "output "Status"(9316)" evaluated to null, but the "component "Conditional Split" (9265)" requires boolean result.

Thanks once again

Nick Maroudas SSC Eights! Points: 977 More actions · Answer 5

Hi, I think you took my two replies and merged them into one.

I didn't think that you would need the derived column object anymore as you needed to do certain things if the value returned was one. So by just adding the conditional split as explained and then continue processing based on the outputs.

To use the derived column with the condition you want the expression has to be boolean so the expression has to return either a true or false like a BIT field, thats why something like ISNULL works because it returns either true or false and based on those results it continues with the true or false statement.

Your colunm is an integer and therefore 1 or 0 is exactly what the are and doesn't mean True or False, If you really need to use a derived column and the results returned into that field are either 1,0 or NULL then you should change the datatype of that field to a BIT datatype.

I hope this helps.

rui_fro SSC-Addicted Points: 467 More actions · Answer 6

The field to be updated is already type bit. I've also tried one thing:

Conditional Split with 2 conditions (OutputSalesHeaderStatus == 1 and OutputSalesHeaderStatus !=1), then for the 2 outputs I put 2 derived columns (to update the bit field) but the problem is that both my destination tables aren't populated.

Maybe some conditions in Conditional Split are missing. The result is that the information is not being catched.

Sorry the trouble but I'm newbie in SSIS

Nick Maroudas SSC Eights! Points: 977 More actions · Answer 7

Ok this is my last try at answering this, as I hope this can help or I'm just misunderstanding what you are trying to do :

You probably wont need the conditional split now because you can determine the value of the field using the derived column component.

In the derived column : Derived Column Name : Select the BIT field you are going to update.

In the derived column column select replace whatever the field name is.

In the expression column try this :

(testint == 1) == (DT_BOOL)"TRUE" ? (DT_BOOL)"TRUE" : (DT_BOOL)"FALSE"

Where testint is the integer field.

I have set up atest package and this works for me. After the conditional split of the integer field is 1 which is what it should be I put the derived column and used the above expression which changed the bit field.

rui_fro SSC-Addicted Points: 467 More actions · Answer 8

rui_fro

SSC-Addicted

Points: 467

May 9, 2008 at 9:20 am

#813148

thanks for the patience.