CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Maiwand
Maiwand

Reputation: 187

Case Statement Error when using MINUS Snowflake DB

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;

Upvotes: 1

Views: 3804

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

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

Upvotes: 2

Popeye
Popeye

Reputation: 35910

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

Upvotes: 1

Related Questions