Hi there !
I’m trying to use dynamic query from BigQuery in dbt but it’s not working. I have tried to convert it with Jinja thanks to ChatGPT but it’s not working anymore. Do you know how can I run the following query in a dbt model ?
DECLARE dynamic_sql STRING; SET dynamic_sql = ( SELECT STRING_AGG(DISTINCT CONCAT(' MAX(CASE WHEN field_name = "', field_name, '" THEN field_value END) AS `', REGEXP_REPLACE(field_name, '[^a-zA-Z0-9_]', '_')), '`, ') FROM jira.all_latest_fields ); SET dynamic_sql = CONCAT('SELECT issue_id, ', dynamic_sql, '` FROM jira.all_latest_fields GROUP BY issue_id'); EXECUTE IMMEDIATE dynamic_sql;
Many thanks for your time !