Conditionals on derived columns

Hi,

Here's my current query, which throws an error that "AgeCalc" is an invalid column in the WHERE clause:

----------
SELECT
.
.
.,
AgeCalc =
CASE
WHEN dateadd(year, datediff (year, B.DOB, B.DateIn), B.DOB) > B.DateIn
THEN datediff (year, B.DOB, B.DateIn) - 1
ELSE datediff (year, B.DOB, B.DateIn)
END

FROM
ResidentData B

WHERE
(AgeCalc >= 18)
----------

How do I do conditionals on the "AgeCalc" derived column?

Thanks.How do I do conditionals on the "AgeCalc" derived column?

Thanks.

You have to write the expression over again:
WHERE
CASE
WHEN dateadd(year, datediff (year, B.DOB, B.DateIn), B.DOB) > B.DateIn
THEN datediff (year, B.DOB, B.DateIn) - 1
ELSE datediff (year, B.DOB, B.DateIn)
END >= 18

Alternatively, write a view that includes your derived column and then you can use your column name in an expression.

I don't recommend using CASE statements in WHERE clauses. It can result in sub-optimal query execution plans.

Regards,

hmscott|||Thanks for your help - I will test the solution and see what the performance is like.

The current situation does not allow me to consider creating views, so I'll have to stick to keeping the query similar to the way it already is.|||select *
from (
SELECT ...
, AgeCalc =
CASE WHEN dateadd(year
, datediff(year, B.DOB, B.DateIn)
, B.DOB) > B.DateIn
THEN datediff(year, B.DOB, B.DateIn) - 1
ELSE datediff(year, B.DOB, B.DateIn)
END
FROM ResidentData B
) as T
WHERE AgeCalc >= 18|||Thanks guys. Both solutions worked well. I will use the second one since it's about half a second faster.

Conditional statement with a cast from string to date

My source file is showing column 10 as string. My destination table is datetime. I am using the derived transformation with a conditional statement. How do I convert the value from string to date. Everywhere I try the (DT_DATE) I get an error.

[Column 10] == "01/01/0001" ? " 01/01/1801" : [Column 10] <= "12/31/1801" ? "12/31/1801" : [Column 10]

What's the error?|||

I modified it to the following but I get an error when I try to debug.

[Column 10] == "01/01/0001" ? (dt_date)" 01/01/1801" : [Column 10] <= "12/31/1801" ? (dt_date)"12/31/1801" : (dt_date)[Column 10]

Error message is:

...conversion between types dt_str and db_timestamp is not supported

|||What is the output column data type specified as in the derived column?|||Where do I check that? I only see the input defined in the derived column transformation which is dt_string 50. The column is defined as datetime in the table.|||http://ssistalk.blogspot.com/2007/01/derived-column.html

I would expect to see the data type of the derived column be DT_DBTIMESTAMP (or DT_DBDATE). The expression should be:

[column10] == "xxxxxx" ? "01/01/1801" : ......

Make sure "Derived Column" is set to "add as new column".|||I don't want to add it as a new column. I am using the conditional statement instead of the SQL case statement. I have dates from Oracle that are outside SQL's range that I need to convert.|||Right, but you can't replace the column because it's a DT_STR.... So if you want a date data type, you need to have a new column. This is the proper way to do it. Then in the data flow, you just ignore [column10] and use [DateColumn10], for instance.|||I tried that but now I get the same error for my new column10.|||

RMooreFL wrote:

I tried that but now I get the same error for my new column10.

Okay, I've posted a new example.

http://ssistalk.blogspot.com/2007/01/derived-column.html

Conditional IF in a derived column transform

HI, I was wondering if there is a possibility to use a confitional if like this:

IF(ISNULL(mycolumn value, "new value if null", mycolumnvalue)

into a derived column transform to infer a value to a null column value. I do know I can do it using a script component by it would be simpler to do by using an expression.

Thank you,

Ccote

Yes, you can do this. Look here in BOL for conditional operator: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/d38e6890-7338-4ce0-a837-2dbb41823a37.htm

-Jamie

|||

Thank you Jamie. I always forget to look in BOL.

Thank you again

Ccote

Conditional Expression quirk?

When using the conditional expression in a derived column transformation, I found that the following expression:

[F1Depth]==3 ? [F2Name] + "--" + [F1Name] : [F1Name]

is invalid while

[F1Depth]==3 ? [F2Name] + "--" + [F1Name] : "" + [F1Name]

is valid.

In both cases, the output type is set to Unicode String (DT_WSTR) with 4000 characters. The error in the first case is:

Error at Data Flow Task [Derived Column (2784)]: Failed to set property "Expression"on "output column" "FactorName" (2918).

Would this be considered a bug, or is there a reasonable explanation?

Thanks,

Anna.

Some more information: The columns F1Name & F2Name are output columns from a lookup that is based on a SELECT statement and their underlying data type/length are varchar (7900)

It seems like the Expression compiler is unable to map this varchar length to any data type?

|||

Is it possible that you have NULL data?|||Expressions are limited to 4000 characters.|||

Phil,

Thanks for your response. However, adding an empty string before the string column makes the expression valid. Why is that?

Thanks,

Anna.

|||Could it have something to do with the ANSI to Unicode conversion? If you explicitly cast F1 to Unicode, does it work?

|||

Thanks for the suggestion.

Either of these works fine:

(DT_STR, 4000, 1252) (F1Depth == 3 ?F2Name + "--" + F1Name : F1Name) and the output set to string [DT_STR]

or

(DT_WSTR, 4000) (F1Depth == 3 ?F2Name + "--" + F1Name : F1Name) and the output set to Unicode string [DT_WSTR]

Therefore, I don't think this is related to ANSI to Unicode conversion. It seems like when the source string is > 4000 characters, I need to either explicitly cast it? Adding an empty string "" to the actual string also seems to force the type.

I do get a warning about the length in both cases, but that is not really an issue.

Is this related more to the length of the source column and the 4000 character restriction in the expression?

|||

Annapurni wrote:

Therefore, I don't think this is related to ANSI to Unicode conversion. It seems like when the source string is > 4000 characters, I need to either explicitly cast it? Adding an empty string "" to the actual string also seems to force the type.

I think you hit the problem right there - In your original code, it thinks you are trying to return a DT_WSTR, but you are returning a DT_STR. Since any literal string value is interpreted as Unicode by SSIS, appending an empty string is forcing the conversion of the DT_STR to a DT_WSTR.

I think. Not 100% positive, though.

Conditional Computed Columns

I have a table with three columns namely "A","B", anc "C".
Column C is a computed column which is derived from Column "B".Something like " IF B < 100 then C=B*0.5 ELSE C=B*0.25"
How do I make it possible in Sql Server?Is there a way of doing this?
Thanks!Use the case when statement. So for your example:

select a,b, case when b < 100 then b*.5 else b*.25 end as c
from ...|||To make ColC a computed column
drop table test
go
create table test
(
colA int,
colB int,
colC as case when ColB < 100 then ColB*0.5 else ColB*0.25 end
)

go
insert test (ColA,ColB) values (100,80)
insert test (ColA,ColB) values (100,100)
go
select *
from test

Output

colA colB colC
---- ---- -----
100 80 40.00
100 100 25.00|||It's working now|||humm.. This really new for me.

Would this create a Trigger??

Originally posted by achorozy
To make ColC a computed column
drop table test
go
create table test
(
colA int,
colB int,
colC as case when ColB < 100 then ColB*0.5 else ColB*0.25 end
)

go
insert test (ColA,ColB) values (100,80)
insert test (ColA,ColB) values (100,100)
go
select *
from test

Output

colA colB colC
---- ---- -----
100 80 40.00
100 100 25.00|||No it doesn't create a trigger, however it does store the computed information in syscomments