Derived Column or script

Dear All,

I am pretty new to SQL2005 technologies.

I have a table like this:

Name

Code

Peter

10

Eric

20

I am interested in translation of the “code” to “job role”:

Name

Code

Job role

Peter

10

Director

Eric

20

Consultant

I have tried to use Derived Column and also script – but I am unable to get it to work.

Any hints/solution would greatly appreciate.

Best Regards, T

A simple example without NULL checking etc

In the 'Derived Column' task add a new column 'Job Role' and set the expression to be something like: -

[Code] == 10 ? "Director" : [Code] == 20 : "Consultant"

|||

Sagestore wrote:

A simple example without NULL checking etc

In the 'Derived Column' task add a new column 'Job Role' and set the expression to be something like: -

[Code] == 10 ? "Director" : [Code] == 20 : "Consultant"

Does not seem like the correct syntax, I think it should be:

[Code] == 10 ? "Director" : ( [Code] == 20 ? "Consultant" : "Unknown" )

|||

Do you have the translations stored in a table somewhere? If so, the LOOKUP component can be used.

-Jamie

|||

The translation is hardcoded which is fine.

However the number of options are more than 2 "consultan" and "director" - at least up to 5 different options.

So I need someting like:

Case 10 => Director

Case 20 => Consultant

Case 30 => Admin

Etc.

Thanks for input so far - but still im waiting for a soluion.

T

|||

Michael gave you the solution - you just need to extend it to cover all eventualities:

[Code] == 9999 ? "Something else!" : ([Code] == 30 ? "Admin" : ([Code] == 10 ? "Director" : ( [Code] == 20 ? "Consultant" : "Unknown" )))

-Jamie

|||

Thanks all of you - now I finally got it to work.

I am extremely happy with this kind of support.

Thanks again and take care, T