SQL Query doesn't work in Azure SQL Data Warehouse (Synapse) why not?

Question

I have an SQL query that works in an on-premise SQL Database, but when I try to execute it on an Azure SQL Data Warehouse, I get an error. Does anyone know another way of writing this SQL Query so that it will work in Azure DW?

SQL Query is:

SELECT MAX(Dates) MostRecentDate FROM (VALUES('2020-01-01'), ('2021-01-01')) AS t(Dates)

Azure DW Error is:

Msg 103010, Level 16, State 1, Line 1 Parse error at line: 1, column: 39: Incorrect syntax near '('.

Here is the full SQL Query (real table names have been removed)

SELECT t1.reference, dd.closest_date, d1.date_one, d2.date_two, d3.date_three
FROM dbo.table1 AS t1
LEFT JOIN dbo.table2 AS t2 ON t1.table2_id = t2.id
LEFT JOIN dbo.table3 AS t3 ON t1.table3_id = t3.id
LEFT JOIN dbo.table4 AS t4 ON t3.table4_id = t4.id
LEFT JOIN dbo.table5 AS t5 ON t4.table5_id = t5.id
LEFT JOIN dbo.table6 AS t6 ON t5.table6_id = t6.id
OUTER APPLY (SELECT CASE WHEN t3.outcome IS NULL THEN '5000-01-01' ELSE ISNULL(t3.outcome_date,'5000-01-01') END AS date_one) AS d1
OUTER APPLY (SELECT ISNULL(t2.outcome_date,'5000-01-01') AS date_two) AS d2
OUTER APPLY (SELECT ISNULL(t6.outcome_date,'5000-01-01') AS date_three) AS d3
/*the below works in normal SQL, but doesn't work in Azure SQL!!!*/
--  OUTER APPLY (SELECT MIN(Dates) closest_date FROM (VALUES(d1.date_one),(d2.date_two),(d3.date_three)) AS t(Dates)) AS dd

question from:https://stackoverflow.com/questions/66066124/sql-query-doesnt-work-in-azure-sql-data-warehouse-synapse-why-not

Answer 1

The Table Value Constructor is nice but is not fully supported in Azure Synapse Analytics as per the documentation, ie Synapse is absent in the Applies to list at the top of the linked doc. VALUES is supported for single rows as per this example, but the easiest fix for your example is to simply rewrite as a simple UNION ALL statement, eg

SELECT t1.reference, dd.closest_date, d1.date_one, d2.date_two, d3.date_three
FROM dbo.table1 AS t1
    LEFT JOIN dbo.table2 AS t2 ON t1.table2_id = t2.id
    LEFT JOIN dbo.table3 AS t3 ON t1.table3_id = t3.id
    LEFT JOIN dbo.table4 AS t4 ON t3.table4_id = t4.id
    LEFT JOIN dbo.table5 AS t5 ON t4.table5_id = t5.id
    LEFT JOIN dbo.table6 AS t6 ON t5.table6_id = t6.id
    OUTER APPLY (SELECT CASE WHEN t3.outcome IS NULL THEN '5000-01-01' ELSE ISNULL(t3.outcome_date,'5000-01-01') END AS date_one) AS d1
    OUTER APPLY (SELECT ISNULL(t2.outcome_date,'5000-01-01') AS date_two) AS d2
    OUTER APPLY (SELECT ISNULL(t6.outcome_date,'5000-01-01') AS date_three) AS d3

/*the below works in normal SQL, but doesn't work in Azure Synapse*/
    --OUTER APPLY (SELECT MIN(Dates) closest_date FROM (VALUES(d1.date_one),(d2.date_two),(d3.date_three)) AS t(Dates)) AS dd
    OUTER APPLY (SELECT MIN(Dates) closest_date FROM ( SELECT d1.date_one UNION ALL SELECT d2.date_two UNION ALL SELECT d3.date_three) AS t(Dates)) AS dd

It's just syntactic sugar really. They are all set operations at the end of the day.