Azure Data warehouse - User defined function issues

Anyone here had luck with creating and using UDFs on Azure data warehouse database? I am in the middle of migrating an on-prem warehouse from SQL Server 2014 to Azure datawarehouse and I ran into an issue with UDFs.

CREATE FUNCTION dbo.fn_GetImpliedRate (@Multiple float, @term int)
RETURNS float
AS
BEGIN
    DECLARE @ImpInt float
    IF(@Term = 1)
        SET @ImpInt = (select [1] from  dbo.ImpliedRate where Multiple = @Multiple); 
    IF(@Term = 2)
        SET @ImpInt = (select [2] from  dbo.ImpliedRate where Multiple = @Multiple); 
    IF(@Term = 3)
        SET @ImpInt = (select [3] from  dbo.ImpliedRate where Multiple = @Multiple); 
    IF(@Term = 4)
        SET @ImpInt = (select [4] from  dbo.ImpliedRate where Multiple = @Multiple); 

RETURN @ImpInt

END;
GO

This UDF works perfectly on SQL Server 2014. When I create this on Azure data warehouse, it gets created but it doesn't work when I query it. It returns a NULL. I have verified obvious things like whether the target table exists etc. All check. I looked at CREATE FUNCTION documentation for Azure data warehouse and it has an example UDF that converts int to a decimal. This works flawlessly on Azure DW. The moment I write a simple function that has a select, it fails. Unfortunately Azure's documentation here is not really helpful and I was wondering if any of you ran into this issue. If yes, how did you resolve?

I just tested another use case and it also doesn't work:

```
CREATE function [dbo].[fn_GetNumberBusinessDays]
(
@StartDate datetime,
@EndDate Datetime
)
returns int
as
begin

DECLARE @NDAYS INT = 0
SELECT @NDAYS =
ISNULL( (DATEDIFF(dd, @StartDate, @EndDate) + 1)
-(DATEDIFF(wk, @StartDate, @EndDate) * 2)
-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END) - 1 ,0) + 1

SELECT @NDAYS = @NDAYS - COUNT(*)
FROM dbo.FedHolidays
WHERE DateOfHoliday BETWEEN @StartDate AND @EndDate

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?

StackExchange Database Administrators Q#139213, answer score: 6