CODEWITHSUNDEEP
viewsnowflake-cloud-data-platform
astonishingme
astonishingme

Reputation: 45

not working in snowflake - "IFF" statement

I am trying to implement below statement in Snowflake view

IFF("Closed" = TRUE,DATEDIFF(DAY,TO_DATE("ActualFinishDate"),current_date()),FALSE) - get True/False

and it wont work if I give IFF("Closed" = TRUE,'1234',FALSE) - this works and i get 1234 or False based on value of closed

can someone please help me why DATEDIFF(DAY,TO_DATE("ActualFinishDate"),current_date()) is not giving result within IFF I tested the statement is correct and right value if i execute it separately.

Upvotes: 1

Views: 2303

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175556

It is all about data types

CASE

In the second form of CASE, each value is a potential match for expr. The value can be a literal or an expression. The value must be the same data type as the expr, or must be a data type that can be cast to the data type of the expr.

The difference is as follow:

DATEDIFF returns INTEGER, BOOLEAN
vs
'1234'   string literal,  BOOLEAN

And for Booelan it works as follow:

TO_BOOLEAN:

For a text expression, string must be:

        'true', 't', 'yes', 'y', 'on', '1' return TRUE.

        'false', 'f', 'no', 'n', 'off', '0' return FALSE.

        All other strings return an error.

Strings are case-insensitive.

For a numeric expression:

        0 returns FALSE.

        All non-zero values return TRUE.

EDIT:

In order to return string instead of boolean explicit cast could be used:

IFF("Closed" = TRUE,TO_VARCHAR(DATEDIFF(DAY,TO_DATE("ActualFinishDate"),current_date())),FALSE)

-- "Closed" column is probably boolean so there is no need to ` = TRUE`:
IFF("Closed",TO_VARCHAR(DATEDIFF(DAY,TO_DATE("ActualFinishDate"),current_date())),FALSE)

Upvotes: 3

Related Questions