Converting float to decimal

Question

Converting float to decimal

rs80

SSCrazy

Points: 2312
More actions
September 16, 2013 at 12:41 pm

#279865

In a derived column transformation, I have this LEN([Glbl Amt]) > 0 ? (DT_NUMERIC, 18, 7) [Glbl Amt] : NULL(DT_NUMERIC,18,7).
The error I'm getting is this:
The function "LEN" does not support the data type "DT_R8" for parameter number 1. The type of the parameter could not be implicity cast into a compatible type for the function. To perform this operation, the operand needs to be explicitly cast with a cast operator.
What I'm trying to do is convert the field Glbl Amt from a float (data type in the excel file) to a numeric(18, 7). But I get the error above.
Thanks for the help.

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

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

Phil Parkin SSC Guru Points: 246964 More actions · Answer 1

Have you tried using a data conversion task?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

michal.lisinski SSCertifiable Points: 6933 More actions · Answer 2

Hi Rs80

I'm afraid you are unable to use LEN function directly, the character_expression argument can be

a DT_WSTR, DT_TEXT, DT_NTEXT, or DT_IMAGE data type.

Try LEN((DT_WSTR,15)([Glbl Amt])) > 0 ? (DT_NUMERIC,18,7)[Glbl Amt] : NULL(DT_NUMERIC,18,7)

or

([Glbl Amt]) != 0 ? (DT_NUMERIC,18,7)[Glbl Amt] : NULL(DT_NUMERIC,18,7)

Br.

Mike

rs80 SSCrazy Points: 2312 More actions · Answer 3

Thanks Mike. I'll try that.

Phil, I wanted to use a derived column transformation before the data conversion transformation to handle blank values as NULL.