Question

Cannot create a derived table using SQL

  • 30 December 2021
  • 2 replies
  • 72 views

Hi, I’m new to Looker, and I’m trying to do the following: 

  1.  Take a lengthy SQL query that I built and save it as a SQL-based derived table, 
  2.  Build a dashboard against the table that I constructed in (1). 

When I try to complete (1), I’m encountering the following error:  ERROR: syntax error at or near "view" Position: 116.  Here is my script: 

view: [my table name] {
  derived_table: {
    sql:

[My long query that begins using a WITH clause]
 ;;
  }
}

Should I be running this query in a special environment or using a different methodology?  I’m noticing that several other NDTs created by someone who used to work at my company are named lr$lw[long alpha-numeric string]_ndt_[table name].  Is my issue that I should be following a similar syntax?  

 

Thanks for whatever help you can provide. 

 

Chris 


This topic has been closed for comments

2 replies

Hello there!,

I think the problem here is with the WITH clause that’s being used at the beginning of the query, as it will create nested WITH statements, as described in the documentation (on the “avoid using common table expressions (CTEs)” part).

You can use the recommendation suggested there, which is described a bit more here.

Thanks, Ricardo - I got this to work.  Appreciate help.