Solved: Bad SQL generated from Explore - Page 2

skorpio11-16412 · 03-07-2022 07:54 AM

Background

Creating and Explore with an “is in the past” 10 days filter generates the following where clause

WHERE ((( CAST(profiles.date as date) ) >= ((TIMESTAMP_ADD(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY), INTERVAL -9 DAY))) AND ( CAST(profiles.date as date) ) < ((TIMESTAMP_ADD(TIMESTAMP_ADD(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY), INTERVAL -9 DAY), INTERVAL 10 DAY)))))

Which generates an

invalidQuery: No matching signature for operator >= for argument types: DATE, TIMESTAMP. Supported signature: ANY >= ANY at [6:9] error message.

Using SQL runner, casting the timestamp output to Date as follows:

WHERE ((( CAST(profiles.date as date) ) >= date((TIMESTAMP_ADD(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY), INTERVAL -9 DAY))) AND ( CAST(onn_profiles.date as date) ) < date((TIMESTAMP_ADD(TIMESTAMP_ADD(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY), INTERVAL -9 DAY), INTERVAL 10 DAY)))))

Corrects the error.

Question

What is the best way to correct the auto generated SQL from looker to avoid comparing Date with Datetime?

leobardor

Hi Skorpio.

Hope you are doing great.

I suggest the following steps to achieve this dimension :

1.- Identify the original datatype of the column of the database

(SQL Dialect, SQL datatype)

2.- CAST to date in the dimension group according to your SQL dialect.

Sharing LookML definition, SQL datatype and SQL dialect will help.

Regards!

Leo

View solution in original post