# Expression with multiple IFs

• karen.ferrara

Right there with Babe

Points: 763

I am trying to create an expression to derive a data column value based off two other data columns.  In English

If VARA is greater than three characters long an the first character is "1" then VARB = VARC + ".A" + the last two characters of VARA

ELSE

If VARA is greater than three characters long an the first character is "2" then VARB = VARC + ".B" + the last two characters of VARA

ELSE

If VARA is greater than three characters long an the first character is "3" then VARB = VARC + ".C" + the last two characters of VARA

ELSE VARD

Here is my expression but it is only converting the VARA values that start with 1.  Can anyone tell me what is wrong with the expression?  I can probably do this using a SQL script but wanted to do it in a derived column container in my data flow.  Any help is appreciated!

LEN(TRIM(VARA)) > 3 && LEFT(VARA,1) == "1" ? (DT_STR,11,1252)RTRIM(VARB) + ".A" + RIGHT(RTRIM(VARA),2) : LEN(TRIM(VARA)) > 3 && LEFT(VARA,1) == "2" ? (DT_STR,11,1252)RTRIM(VARB) + ".B" + RIGHT(RTRIM(VARA),2) : LEN(TRIM(VARA)) > 3 && LEFT(VARA,1) == "3" ? (DT_STR,11,1252)RTRIM(VARB) + ".C" + RIGHT(RTRIM(VARA),2) : (DT_STR,11,1252)VARD

• Phil Parkin

SSC Guru

Points: 244656

If it were me, I think I'd resort to a script component, as there are too many nested IFs for my liking.

However, things could be made much easier on the eye if you create a few derived columns first. Something like:

• (DT_STR,11,1252)TRIM(VARA)
• (DT_STR,11,1252)TRIM(VARB)
• (DT_STR,11,1252)TRIM(VARD)

Then, your expression (the overall syntax/logic looks fine) becomes simpler:

`LEN(VARAx) > 3 && LEFT(VARAx,1) == "1" ?   VARBx + ".A" + RIGHT(VARAx,2) :   LEN(VARAx) > 3 && LEFT(VARAx,1) == "2" ?     VARBx + ".B" + RIGHT(VARAx,2) : 	LEN(VARAx) > 3 && LEFT(VARAx,1) == "3" ? 	  VARBx + ".C" + RIGHT(VARAx,2) : 	  VARDx`

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.
See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

Viewing 2 posts - 1 through 2 (of 2 total)