two type conversions in one derivation

[pattemk]

Hi,

Iam trying to put something like below if then else condition in the derivation grid of my transformer.

If TRIM(DSLink219.COL5) = 'DATE' Then StringToDate(DSLink219.FIELD5,"%yyyy-%mm-%dd") Else StringToTimestamp(DSLink219.FIELD5,"%yyyy-%mm-%dd %hh:%nn:%ss")

I am getting the following compilation error, "Error in output column derivation expression for column FIELD5 in link DSLink232. Invalid conversion requested from a timestamp to a date."

I think it is not the proper way to two kinds of type conversions in one derivation but is there any other way of achieving the abvoce condition using one transformer.

Please advice

[prasannakumarkk]

What is the target data type?

Guess you are trying acheive this?

Code: Select all

StringToTimestamp((If TRIM(DSLink219.COL5) = 'DATE' Then DSLink219.FIELD5 || "00:00:00" Else DSLink219.FIELD5),"%yyyy-%mm-%dd %hh:%nn:%ss") 

[pattemk]

Thanks for replying, but the target metadata will be date if the COL5 = 'DATE' and will be timestamp otherwise.


PLease advice

[chandra.shekhar@tcs.com]

A column cannot have two different datatypes. Either its a DATE or TIMESTAMP.

[harishkumar.upadrasta]

Please convert it either to Date/ Timestamp depending on Target Data type. A column can have a single data type.

[BI-RMA]

If, however, You are sending the conversion-result down to different targets with different datatypes, use two different columns for the derivation, even if the column-names happen to be identical in both targets (which is not a good practice, though).

[chulett]

I agree with Prasanna's solution. For whatever that is worth.

[ray.wurlod]

... except, of course, for the incorrect concatenation operator.

[chulett]

Well yah... duh.

[prasannakumarkk]

Oops . regret team.