May 22, 2018 at 1:53 pm
Hi All,
I have a requirement for derived column expression as if ColA ="AB" then display [ColB] else if ColA = " BC" then display 1/[colB]. I tried an expression which is throwing an error as the expression might contain an invalid token, missing parenthesis or not well-formed. but I tried all possible ways to get it right no luck. Please help me with the expression.
(DT_NUMERIC,15,6)(ColA=="AB")?(DT_NUMERIC,15,6) [ColB]:)(1/(DT_NUMERIC,15,6) ColB) -- This should be numeric(15,6) converted other non numeric fields to numeric.
Thank you!
May 22, 2018 at 2:01 pm
neranki9 - Tuesday, May 22, 2018 1:53 PMHi All,I have a requirement for derived column expression as if ColA ="AB" then display [ColB] else if ColA = " BC" then display 1/[colB]. I tried an expression which is throwing an error as the expression might contain an invalid token, missing parenthesis or not well-formed. but I tried all possible ways to get it right no luck. Please help me with the expression.
(DT_NUMERIC,15,6)(ColA=="AB")?(DT_NUMERIC,15,6) [ColB]:)(1/(DT_NUMERIC,15,6) ColB) -- This should be numeric(15,6) converted other non numeric fields to numeric.
Thank you!
I think you have an extra bracket in there. Try this(DT_NUMERIC,15,6)(ColA=="AB")?(DT_NUMERIC,15,6) [ColB]:(1/(DT_NUMERIC,15,6) ColB)
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 22, 2018 at 2:14 pm
Phil Parkin - Tuesday, May 22, 2018 2:01 PMneranki9 - Tuesday, May 22, 2018 1:53 PMHi All,I have a requirement for derived column expression as if ColA ="AB" then display [ColB] else if ColA = " BC" then display 1/[colB]. I tried an expression which is throwing an error as the expression might contain an invalid token, missing parenthesis or not well-formed. but I tried all possible ways to get it right no luck. Please help me with the expression.
(DT_NUMERIC,15,6)(ColA=="AB")?(DT_NUMERIC,15,6) [ColB]:)(1/(DT_NUMERIC,15,6) ColB) -- This should be numeric(15,6) converted other non numeric fields to numeric.
Thank you!
I think you have an extra bracket in there. Try this
(DT_NUMERIC,15,6)(ColA=="AB")?(DT_NUMERIC,15,6) [ColB]:(1/(DT_NUMERIC,15,6) ColB)
Now the error is conditional expression of the conditional operator must return a Boolean, Which is type DT_BOOL
May 22, 2018 at 3:10 pm
neranki9 - Tuesday, May 22, 2018 2:14 PMNow the error is conditional expression of the conditional operator must return a Boolean, Which is type DT_BOOL
Ok, now remove the initial (DT_NUMERIC,15,6).
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 22, 2018 at 3:23 pm
Thanks Phil!
I tried this (DT_STR,1,1252)(ColA=="AB") ? (DT_NUMERIC,10,6) [ColB] : (DT_NUMERIC,10,6)(((DT_NUMERIC,10,6) [ColB] / (DT_NUMERIC,10,6)1)) expression and it worked for me, Thanks a lot for your quick response.
May 23, 2018 at 11:08 am
>> I have a requirement for derived column expression as if col_a ="AB" then display [ColB] else if col_a = " BC" then display 1/[colB].<,
It would really help if you would bother to post DDL, as required by basic netiquette. I'm going to guess that col_a is a character column of some kind and that col_b is some unspecified kind of numeric. You might want to look up what double quote marks mean in SQL before you use them again.
CASE WHEN col_a = 'AB' THEN col_b ELSE 1.0/col_b END AS foobar
>> I tried an expression which is throwing an error as the expression might contain an invalid token, missing parenthesis or not well-formed. <<
Since we have no DDL, we have no way to help you. You would have put a check constraint on your col_a that would've prevented an invalid token.
I have no idea what that other block of non-SQL code was about. It apparently came from a file because you referred to a "field" and we all know that columns and fields are totally different concepts.
Please post DDL and follow ANSI/ISO standards when asking for help.
May 23, 2018 at 11:12 am
neranki9 - Tuesday, May 22, 2018 1:53 PM
>> I have a requirement for derived column expression as if col_a ="AB" then display [ColB] else if col_a = " BC" then display 1/[colB].<,
It would really help if you would bother to post DDL, as required by basic netiquette. I'm going to guess that col_a is a character column of some kind and that col_b is some unspecified kind of numeric. You might want to look up what double quote marks mean in SQL before you use them again.
CASE WHEN col_a = 'AB' THEN col_b ELSE 1.0/col_b END AS foobar
>> I tried an expression which is throwing an error as the expression might contain an invalid token, missing parenthesis or not well-formed. <<
Since we have no DDL, we have no way to help you. You would have put a check constraint on your col_a that would've prevented an invalid token.
I have no idea what that other block of non-SQL code was about. It apparently came from a file because you referred to a "field" and we all know that columns and fields are totally different concepts.
This is SSIS, not T-SQL. The columns may well come from a flat file and therefore there is not necessarily any DDL available. Double quotes are used for string literals in SSIS expressions, so there's no problem there either.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 23, 2018 at 11:27 am
neranki9 - Tuesday, May 22, 2018 1:53 PMThis is SSIS, not T-SQL. The columns may well come from a flat file and therefore there is not necessarily any DDL available. Double quotes are used for string literals in SSIS expressions, so there's no problem there either.
Sarcasm is often wasted. My point is that this is an SQL forum, and that the problem can be easily solved in the DDL when they get the data into the schema.
Please post DDL and follow ANSI/ISO standards when asking for help.
May 23, 2018 at 11:47 am
jcelko212 32090 - Wednesday, May 23, 2018 11:08 AMneranki9 - Tuesday, May 22, 2018 1:53 PMThis is SSIS, not T-SQL. The columns may well come from a flat file and therefore there is not necessarily any DDL available. Double quotes are used for string literals in SSIS expressions, so there's no problem there either.
Sarcasm is often wasted. My point is that this is an SQL forum, and that the problem can be easily solved in the DDL when they get the data into the schema.
Actually, Joe, it's a SQL Server forum (SQL on its own is almost meaningless without an RDBMS to interact with) and SSIS is part of SQL Server. I'm also pretty confident Phil wasn't being sarcastic; his point his extremely valid for the topic you've posted in.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 23, 2018 at 12:26 pm
Phil Parkin - Wednesday, May 23, 2018 11:12 AMjcelko212 32090 - Wednesday, May 23, 2018 11:08 AMneranki9 - Tuesday, May 22, 2018 1:53 PMThis is SSIS, not T-SQL. The columns may well come from a flat file and therefore there is not necessarily any DDL available. Double quotes are used for string literals in SSIS expressions, so there's no problem there either.
Sarcasm is often wasted. My point is that this is an SQL forum, and that the problem can be easily solved in the DDL when they get the data into the schema.
Actually, Joe, it's a SQL Server forum (SQL on its own is almost meaningless without an RDBMS to interact with) and SSIS is part of SQL Server. I'm also pretty confident Phil wasn't being sarcastic; his point his extremely valid for the topic you've posted in.
And, Mr. Celko, based on what the OP posted it was obvious to anyone familiar with MS SQL Server that the question was about SSIS. The give away was this: (DT_NUMERIC,15,6). Obviously NOT T-SQL.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply