DSXchange

Posted: **Thu Jan 12, 2012 6:29 pm**

The datastage job i am developing deals mostly with date columns.

The source columns are a char [8] fields. These fields stores dates in format 20100101 i.e. yyyymmdd

The job should should be able to handle all kind of junk data.

so when ever i am hitting null or invalid date in this field i make it empty '' string in the stage variable. My stage variables are varchar(10).

Then in next various other stage variables i compare this field with other fields like follows

The job fails or throws warning when it tries hit the empty '' string. It cannot convert '' empty from stringtoDate. Not sure how to handle this scenario

Code: Select all

code:

If IsNotNull(orcl.dslink4.ORDER_DATE)    and svOrderDate<>''  and StringToDate(svOrderDate,"%yyyy%mm%dd") < '2010-03-01'
Then 
	'ABC'
Else If StringToDate(svCancelDate,"%yyyy%mm%dd") < StringToDate(svOrderDate,"%yyyy%mm%dd")
Then
	'DEF
Else If StringToDate(svCancelDate,"%yyyy%mm%dd") > StringToDate(svOrderDate,"%yyyy%mm%dd")
Then
	'GHI'
Else
	'XYZ'

I get warning: Copy_of_Transformer_77,0: Conversion error calling conversion routine date_from_string data may have been lost

In some cases job fail with error: Copy_of_Transformer_77,0: Failure during execution of operator logic.

Appreciate help on how to handle this '' empty case scenario.

Posted: **Thu Jan 12, 2012 9:18 pm**

The warning is fairly straight and make sure your svOrderDate contains only valid date as string in YYYYMMDD format.

Posted: **Thu Jan 12, 2012 10:01 pm**

It sounds like you're doing a lot of StringToDate functions even for columns where you already know the date is invalid and have set it to '' empty string. Could you insert a new stage variable like svValidDate before all that other processing and then only do all those StringToDate functions if the date is valid?

Posted: **Thu Jan 26, 2012 7:04 am**

The stage variables cannot handles nulls and '' is an invalid date. use a NulltoValue function and define the value with a default date for processing it. When sending the stage variable's value to the output column replace the default value with null

Posted: **Thu Jan 26, 2012 7:20 am**

The best way Is applying isvalid() before stringtodate()

Posted: **Thu Jan 26, 2012 9:46 am**

You've already converted ORDER_DATE to a stage variable, using null-handling, so there should be no need to perform null handling again in your logic.

Perform the check for empty string by itself (no other conditions in the if expression). If it is not empty, then you can safely perform the other logic which includes the StringToDate() conversions because you know the field is not empty. As Pandeesh recommends, you should include an IsValid() check as well:

Code: Select all

if svOrderDate <> '' and IsValid('date',svOrderDate,"%yyyy%mm%dd") then
   ...all the StringToDate() stuff...
else
  'XYZ'

Regards,

DSXchange

StringToDate on an empty string

StringToDate on an empty string