Derived table under Dimesion in a View file

Looker Q&A Looker
1

Hello experts,

Can we write an SQL query under a Dimension, not in a View file?

I understand that we can use a derived table in a View file, however, I am not sure if the sql query can be wrote for a Dimension.

I created a View file with a derived table. It worked successfully. And then I created a Dimension in that View file but with the following SQL for another derived table.

dimension: dimension_name {

type: number

sql: sum (a)

from (

select field_a, max(field_b) as a

from schema_name.table_name

group by 1) as b

The error message says that the DB encountered an error while running the query. It’s a syntax error at or near “select” in context "table_name ) SELECT select derived_table_name_of_view.

2

The short answer is no - if you look at the generated SQL that is throwing your error, you will see that the new SELECT statement you wrote in your dimension has just been injected straight into the ‘SELECT’ statement for the whole query, which simply will not work.

3

Thank you for the response.

I’ll try to join those derived tables as differemt views then.