Derived Column Using Previous Value!

Question

Derived Column Using Previous Value!

Robert W Marda

SSChampion

Points: 13413
More actions
September 28, 2006 at 7:56 am

#115749

I am using the following expression in a derived column data flow object: REPLACE(thisdate,"","1999-01-01")
The result I get one I run this is that for everywhere in the txt file there is no date it is using the date it fetched from the previous record instead of using "1999-01-01".
The txt file is tab delimited so the times when it puts the empty value into my date column is when there are two tabs in a row.
Any ideas what I am doing wrong? Or do you need more info?
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems

Viewing 4 posts - 1 through 3 (of 3 total)

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

Robert W Marda SSChampion Points: 13413 More actions · Answer 1

As I normally do, I keep looking for a solution after I post here and I just found something I did not expect in the remarks for the REPLACE function it says:

The length of searchstring must not be zero.

So this is something Microsoft is aware of and I've just discovered what happens when I do this, so now my question changes to this:

Does anyone know a way I can get this 0 length empty value converted to NULL or a date at the time I import the data from my txt file?

Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems

Ram Ram Ten Centuries Points: 1079 More actions · Answer 2

You may try like this...

DECLARE @VAL VARCHAR(10)

SELECT @VAL =''

SELECT @VAL =NULL

SELECT CASE WHEN (LEN(LTRIM(RTRIM(@VAL))) = 0 OR @val IS NULL) THEN '1999-01-01' ELSE @val END

Robert W Marda SSChampion Points: 13413 More actions · Answer 3

I have tried using the CASE function in the expression area and it never accepts it plus the CASE function is not listed as one of the functions that can be used in the expression field of a derived column.

I finally found a way to make this work. Sorry for answering my own question but I always keep working on a problem while hoping someone will post something that points me in the right direction.

Here is what I did:

LEN(thisdate) == 0 ? "1900-01-01" : thisdate

Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems