select field list and make new query with this list

[tumikosha]

I would like to select field names from one table and pass their list to another select.
I know how to do this with DECLARE and EXECUTE IMMEDIATE  (BigQuery dialect)
But, they cannot be used inside DERIVED TABLE
How can I model something like this code?

view: complicated_datasource {
  derived_table: {
    sql:

DECLARE FIELD_LIST_AS_STR STRING;



EXECUTE IMMEDIATE """
SELECT STRING_AGG(FIELD) 
FROM ( --- here we query list of fileds from another  Table
       SELECT 'Alert1' as Field,
       UNION ALL SELECT 'Alert2' as Field,
       UNION ALL SELECT 'Alert3' as Field
       )
"""
INTO FIELD_LIST_AS_STR;

EXECUTE IMMEDIATE format("""
    select %s FROM (  --here schould be another table
              SELECT 1 as Alert1, 2 as Alert2, 3 as Alert3
    )
""", FIELD_LIST_AS_STR)

-- a lot of pivoting and other calculations
;;

[Dawid · Accepted solution]

I never tried something like that but perhaps create_process could be worth a try?

https://docs.looker.com/reference/view-params/create_process

or sql_create https://docs.looker.com/reference/view-params/sql_create

[leobardor]

Hi Tumikosha, 

It looks like it should be converted to a Materialized view- Bigquery

Hope this helps!

Regards,

Leo

[tumikosha]

@leobardor I want to use  filters for  date range. Is it possible to pass user input to  Materialized view ?

[tumikosha]

Thanks to  @Dawid . The dynamic scripting works in the create_process steps.

[Dawid]

Awesome news. I only used it when I needed to use for dynamic table creation like CREATE <name> AS but it’s good to know that we can do more stuff with this!