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
;;
Solved! Go to 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
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
Hi Tumikosha,
It looks like it should be converted to a Materialized view- Bigquery
Hope this helps!
Regards,
Leo
@leobardor I want to use filters for date range. Is it possible to pass user input to Materialized view ?
Thanks to @Dawid . The dynamic scripting works in the create_process steps.
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!