What does it mean?
This is a SQL error that is complaining about comparing two different datatypes. You may see a specific error that complains about DATE, TIMESTAMP; VARCHAR, INT; or really any other combination of datatypes. See your dialect's documentation for more information.
An ELI5 example is if you tried to compare a number to a string. Is 1 >= 'orange' ? These ambiguities freak out computers and throw an error. For most dialects, all comparison's must be between compatible datatypes (eg a string vs a string, a number to a number, etc).
The high level steps to resolve this issue would be:
- Identify the specific SQL that is generating the comparison between the two incompatible values
- Identify the datatype of each of the two values/columns
- Add a cast or conversion statement SQL statement, or a LookML parameter that generates casting logic
Example Case 1:
Dimension_Group errors with "No Matching... DATE, TIMESTAMP"
How do I fix it?
As can be gathered from the high level explanation, somewhere a date value is being compared to a timestamp value. A common reason for this is if you've defined a dimension_group with a date instead of a timestamp. A dimension_group expects a timestamp by default, so you will need to either cast the date to a timestamp in the sql parameter or utilize the datatype parameter to call this a date.
A few example solutions:
- Casting the date to a timestamp in
sql:(the SQL will vary by dialect):
dimension_group: created {
type: time
sql: TIMESTAMP(${TABLE}.created_at) ;;
}- Using the datatype parameter:
dimension_group: created {
type: time
datatype: date
sql: ${TABLE}.created_at ;;
}
Example Case 2:
Measure errors with “ERROR: function sum(text) does not exist”
Full error message might look something like this: The PostgreSQL 9.5+ database encountered an error while running this query. ERROR: function sum(text) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts.
How do I fix it?
As the error indicates, we’re trying to use a sum() function on a string value. The SQL function `sum()` expects numeric data. To resolve, we would need to cast the data to some numeric type, referencing our dialect’s documentation for specifics.
A few examples using different methods in differing dialects:
