Azure Data warehouse - User defined function issues
Functions in Azure DW don't support select statements that access tables like in your use case, see CREATE FUNCTION (SQL Data Warehouse):
function_body
Specifies that a series of Transact-SQL statements, which do not reference database data (tables or views), define the value of the function.
Could you double check that function is created in DW?
Azure SQL Data Warehouse has limited support for UDFs. It does not yet support the syntax SELECT @var =
. Instead you must use DECLARE @var int =
or SET @var =
. SQL DW UDFs also do not yet support queries on user tables. Please use our feedback page to vote for new features.
There is a Data Warehouse Migration utility (available here) for Azure SQL Data Warehouse which picks up issues like incompatible datatypes, in-line functions, use of hints, use of RETURN
statement, INSERT ... EXEC
and many others:
Unfortunately it does not pick up scalar functions which reference tables and really it should. However, for your particular function, it could just be a view (or even another table), eg
CREATE VIEW dbo.vw_ImpliedRates
AS
SELECT 1 term, [1] impliedRate, Multiple
FROM dbo.ImpliedRate
UNION ALL
SELECT 2 term, [2], Multiple
FROM dbo.ImpliedRate
UNION ALL
SELECT 3 term, [3], Multiple
FROM dbo.ImpliedRate
UNION ALL
SELECT 4 term, [4], Multiple
FROM dbo.ImpliedRate;
GO
CREATE TABLE dbo.test
(
Multiple FLOAT NOT NULL,
Term INT NOT NULL
);
GO
INSERT INTO dbo.test ( Multiple, Term )
VALUES
( 0.001, 1 ), ( 0.001, 2 ), ( 0.001, 3 ), ( 0.001, 4 );
GO
SELECT impliedRate, v.Multiple
FROM dbo.test t
INNER JOIN dbo.vw_ImpliedRates v
ON t.Multiple = v.Multiple
AND t.Term = v.Term;
SELECT *
FROM dbo.vw_ImpliedRates
WHERE Multiple = 0.001
AND Term = 2
I tried this on my Azure SQL Data Warehouse and it worked perfectly well.
You should also know that scalar functions in SQL Server do not scale well when called against tables, and if you have the volume appropriate for Azure SQL Data Warehouse (ie billions of rows), then you will need to re-think use of scalar functions anyway. For example, using CTAS
and writing more procedural code is a good approach that will allow you to make proper use of this immensely powerful platform.