0

Trying to use IIF() in SELECT statement and getting the error "Incorrect syntax near '>'." in Azure Synapse Analytics-Dedicated SQL pool

Tried like below:

SELECT IIF(1>2, 'YES', 'NO');

enter image description here

This error makes me to believe that IIF statement is not working at all in Azure Synapse Analytics Dedicated SQL Pool.

Knew that, CASE is alternative but want to know Is there any specific reason behind this non-working of IIF in Synapse Dedicated SQL pool?

Any know-how or knowledge share would be much appreciated regarding this!

Improve this question
2
  • 1
    It's clearly identified that this operator is not supported by Synapse analytics: "IIF is not supported in Azure Synapse Analytics Dedicated SQL Pool." => learn.microsoft.com/en-us/sql/t-sql/functions/…. Commented Aug 18, 2021 at 7:09
  • CASE is standard SQL. IIF() is bespoke SQL. I would suggest that you use the method that is standard SQL. Commented Aug 18, 2021 at 10:56

2 Answers 2

Reset to default
1

As Jaime Drq mentioned in comment, At this point of time, IIF is not supported in Azure Synapse Analytics Dedicated SQL Pool and this is at feedback/discussion level alone.

So requested the same in discussions of Tech community of Microsoft. Hopes Azure Synapse Analytics team will implement this simple add-on in nearby future.

If you also want this to be in Azure Synapse Analytics Dedicated SQL Pool, let the community to know this thing and give your likes at Tech community of Microsoft - https://techcommunity.microsoft.com/t5/azure-synapse-analytics/azure-synapse-analytics-dedicated-sql-pool-iif-statement-support/m-p/2659481#M16

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

As per the documentation, IIF is not supported in dedicated SQL pools at this time. The equivalent of your statement using a CASE statement is:

SELECT CASE WHEN 1 > 2 THEN 'YES' ELSE 'NO' END;

IIF is currently by serverless SQL pools:

IIF on serverless

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.