IIF() unsupported by Azure Synapse Analytics databases

[lukebandy]

{dplyr} will translate certain R queries into SQL containing IFF() calls, but this is not supported by all SQL servers (such as Azure Synapse Analytics). This can be worked around, but it is unclear to the user where they went wrong.

Sort of difficult to reproduce without your own Azure Synapse Analytics instance, so here are the generated queries. I tried to reproduce this with a SQLite database but it does not produce the same SQL which makes me wonder if it's dblyr::in_schema() (etc) related.

# Libraries
library(DBI)
library(dplyr)
library(dbplyr)

# Connect to some Azure Synapse Analytics database
conn_azure <- dbConnect(...)

# Produces `IIF`
tbl(conn_azure, in_schema("dbo", "tbl")) |>
  count(group) |>
  mutate(single = n == 1) |> 
  show_query()

SELECT "q01".*, CAST(IIF("n" = 1.0, 1, 0) AS BIT) AS "single"
FROM (
  SELECT "group", COUNT(*) AS "n"
  FROM "dbo"."tbl"
  GROUP BY "group"
) "q01"

# Also produces `IIF`
tbl(conn_azure, in_schema("dbo", "tbl")) |> 
  summarise(single = n() == 1,
            .by = "PSRNumber") |> 
  show_query()

SELECT "group", CAST(IIF(COUNT(*) = 1.0, 1, 0) AS BIT) AS "single"
FROM "dbo"."tbl"
GROUP BY "group"

# Workaround
tbl(conn_azure, in_schema("dbo", "tbl")) |> 
  count(group) |>
  mutate(single = case_when(
    n == 1 ~ TRUE,
    TRUE ~ FALSE)) |> 
  show_query()

SELECT "q01".*, CASE WHEN ("n" = 1.0) THEN 1 ELSE 0 END AS "single"
FROM (
  SELECT "group", COUNT(*) AS "n"
  FROM "dbo"."tbl"
  GROUP BY "group"
) "q01"

# Setup SQLite database
conn_sqlite <- dbConnect(RSQLite::SQLite(), 'database.sqlite')
dbWriteTable(conn_sqlite, "tbl", overwrite = TRUE, data.frame(
  group = c("a", "a", "b", "c")))

# Doesn't produce the same SQL as the first example
tbl(conn_sqlite, "tbl") |>
  count(group) |> 
  mutate(single = n == 1) |> 
  show_query()

SELECT `q01`.*, `n` = 1.0 AS `single`
FROM (
  SELECT `group`, COUNT(*) AS `n`
  FROM (tbl) AS `q01`
  GROUP BY `group`
) AS `q01`

[lukebandy]

Sorry - we've realised this is a duplicate of tidyverse/dbplyr#843 - will close