Search code examples
azure-synapseazure-data-factory

mapping data flow column pattern type =='decimal' not changing decimal columns

I'm using a column pattern to catch nulls. My logic is very simple.

Matching condition

type=='decimal'

Column name expression

$$

Value expression

coalesce($$,toDecimal(0,38,18))

I can't get this to work it simply leaves NULL values in place. I can however use the expression

type!='string' && type!='date'

This does change the value of the columns with NULL values to 0. In my current case this is fine I have strings, dates and decimals but I can see how this is not ideal as I might have integers, doubles or other types and I would not want these to be converted to decimals and the list of && statements becomes quite long.

Can anyone supply details of how I should specify a decimal type or a better workaround if not? If there is a link to any good references would be helpful too, the official documentation is not especially specific.

To confirm I am working with decimal values: showing decimals

Solution

  • You can also use typeMatch(type, 'number') if you want to match all number types. Per the documentation, other options are:

    typeMatch(type, 'integral') /* matches short, integer, long */
typeMatch(type, 'fractional') /* matches double, float, decimal */
typeMatch(type, 'datetime') /* matches date, timestamp */