What's wrong with this expression? (@[User::InsurerID] == 101 ? "47")

Question

What's wrong with this expression? (@[User::InsurerID] == 101 ? "47")

stricknyn

SSCertifiable

Points: 6560
More actions
January 23, 2008 at 9:04 am

#120291

Hello everyone,
I can't seem to find the problem with this expression: (@[User::InsurerID] == 101 ? "47").
InsurerID is a variable that I basically want to create another column based off of. If it's 101, 102, 13, etc. I want the new column to the new value. For demonstration purposes, I only included 1 if statement. See how I have the derived column task set up below:
Derived Column Name: MCO_CODE
derived Column:
Expression: @[User::InsurerID] == 101 ? "47"
Data Type: string [DT_STR]
Length: 50
code Page: 1252 (ANSI - Latin I)
I'm doing this for something else and it works great (with the exception of it being a variable), so I can't seem to figure out the problem with the expression. Anyone see any problems?
Thanks!
Strick

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

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

Tim Mitchell SSCoach Points: 15681 More actions · Answer 1

It looks like you're trying to use a ternary operator, but you've left out the third operand. A ternary statement will contain an evaluation test, a value for true, and a value for false. So you need to set up a "false" value:

@[User::InsurerID] == 101 ? "47" : "some other value here"

hth,

Tim

Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices

stricknyn SSCertifiable Points: 6560 More actions · Answer 2

Sweet thanks! Actually my expression will always return a value. I guess I'll just set the ternary = 0 even though it'll never happen.

Thanks,

Strick

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 3

I guess I don't understand... if a conditional expression will always result in TRUE, why have the conditional expression to begin with? What am I missing here? Keep in mind that I know squat about SSIS, please. 😉

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

stricknyn SSCertifiable Points: 6560 More actions · Answer 4

Yeah, that's what was weird to me too. But if I have to use the "else" condition, I'll just set it to 0.

Strick

Tim Mitchell SSCoach Points: 15681 More actions · Answer 5

What Jeff is saying is that if you are always returning the same value, why use the ternary operator at all? Just make your expression "43" to return that value every time.

Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices

stricknyn SSCertifiable Points: 6560 More actions · Answer 6

Hi all,

Because it won't return the same number every time. In my I just wanted to keep it simple; I knew that the issue I was havng was syntax. My statement was that it was weird that I have to have the "else" part of my equation even if I don't use it (I come from a programmer's background)

So for example, in programming I can say

If A = B then C and if C = B then E

and leave it at that. Seems like in an SSIS expression, I have to use the third "else" part. So the statement above would be.

If A = B then C and if C = B then E else F.

So in my case there never will be an "else". Hope I'm making sense.

Thanks all,

Strick

shirodkar.prasad Grasshopper Points: 16 More actions · Answer 7

i want to give complete filepath dynamic.

filename is fixed.

filepath value is present in user variable @[User::FileShareLoc] .

@[User::FileShareLoc] + "\\ABC.csv"

its giving error

how to write expression.