databricksdatabricks-sql

T-SQL Conversion to Databricks SQL with CONV Function

I'm trying to convert the following T-SQL to Databricks SQL

T-SQL

CONVERT(DECIMAL(26,8),
            ISNULL(SIB.ClientConsideration * ISNULL(RH.ExchangeRateLedger, (1 / NULLIF(ST.TradeDateFx, 0))), ST.TradeAmountBase)) AS TradeAmount

Databricks SQL

CONV(DECIMAL(26,8),
            IFNULL(SIB.ClientConsideration * IFNULL(RH.ExchangeRateLedger, (1 / NULLIF(ST.TradeDateFx, 0))), ST.TradeAmountBase)) AS TradeAmount

With the above conversion I get the error:

Function decimal accepts only one argument

When I enter just one argument I get the following error:

Invalid number of arguments for function conv. Expected: 3; Found: 2

This is a little confusing.

Can someone let me know where I'm going wrong?

Solution

  • I guess what you are trying to do is to CAST it to DECIMAL type. CONV in databricks is different than in T-SQL. You can achieve that with:

    CAST(IFNULL(SIB.ClientConsideration * IFNULL(RH.ExchangeRateLedger, (1 / NULLIF(ST.TradeDateFx, 0))), ST.TradeAmountBase) AS DECIMAL(26,8)) AS TradeAmount