azure-databricksdatabricks-sql

Databricks Error AnalysisException: Invalid number of arguments for function isnull. Expected: 1; Found: 2;

I am refactoring T-SQL code to Databricks SQL

I am having an issuing with the isnull function.

I get the following error

Invalid number of arguments for function isnull. Expected: 1; Found: 2;

The code is as follows:

ISNULL(SIB.Consideration * ST.Ledger, ST.AmountLocal)) AS Amount

Any thoughts on where I have made the error with the above ISNULL function.

Solution

  • If you look at the Databricks ISNULL documentation, you can see that it requires only a single argument.

    What you need is the IFNULL function. In your case, I'm assuming that you looking to get a response of SIB.Consideration * ST.Ledger and if this value is NULL, then respond with ST.AmountLocal. In that case, your Databricks SQL will be something like this.

    ifnull(SIB.Consideration * ST.Ledger,ST.AmountLocal) AS Amount