I´m trying to change a specific date to a other one in order to compare the hash values of two queries. For that I´m using the CASE statement. But I can´t understand why I´m getting the following error
The column DATE_VALUE has the data type DATE and I´m operating on a Snowflake Database
inconsistent data type for result columns for set operator input branches, expected DATE, got BOOLEAN for expression [{2}] branch {3}
SQL statement
SELECT
COL
, CASE
WHEN DATE_VALUE = '2021-01-10'
THEN DATE_VALUE = CAST('2021-01-11' AS DATE)
END AS DATE_VALUE
,HASH(
CASE
WHEN DATE_VALUE = '2021-01-10'::DATE
THEN DATE_VALUE= '2021-01-11'::DATE
END
, COL
, COL
, COL
, COL
, COL
, COL
, COL
)
FROM TABLE1
MINUS
SELECT
COL
, DATE_VALUE
, HASH(
DATE_VALUE
, COL
, COL
, COL
, COL
, COL
, COL
, COL
)
FROM TABLE2;
2 Answers
You have this expression for the second column:
(CASE WHEN DATE_VALUE = '2021-01-10'
THEN DATE_VALUE = CAST('2021-01-11' AS DATE)
END) AS DATE_VALUE
The THEN clause is:
DATE_VALUE = CAST('2021-01-11' AS DATE)
This is a boolean expression, not a date expression. You seem to want:
(CASE WHEN DATE_VALUE = '2021-01-10'
THEN CAST('2021-01-11' AS DATE)
END) AS DATE_VALUE
Or:
(CASE WHEN DATE_VALUE = '2021-01-10'
THEN '2021-01-11'::DATE
END) AS DATE_VALUE
CASE..WHEN is incorrect.
It should be
CASE
WHEN DATE_VALUE = '2021-01-10'
THEN CAST('2021-01-11' AS DATE) -- DATE_VALUE = CAST('2021-01-11' AS DATE) returns boolean not date
END AS DATE_VALUE
