PfLCHNE7

1
2DO $$
3DECLARE
4  v_sql_statement  TEXT;
5BEGIN
6
7    SELECT
8        ('DROP TABLE IF EXISTS  ws_mkt_dst.tb_b2b_cjs_tel_week_agg CASCADE;'
9        || 'CREATE TABLE ws_mkt_dst.tb_b2b_cjs_tel_week_agg AS '
10        || 'SELECT accounts.*,'
11        || ( SELECT STRING_AGG(column_name, ', ')
12            FROM (
13            SELECT DISTINCT column_name
14            FROM information_schema.columns
15            WHERE table_schema = 'ws_mkt_dst'
16                AND table_name like 'tb_tmp_b2b_gbl_cjs_%_week_agg'
17                AND column_name NOT IN ('analytic_acct_id','fisc_wk_strt_dt','help'))t)
18        || ' FROM ('
19        ||   'SELECT DISTINCT analytic_acct_id,'
20        ||                    'weeks.fisc_wk_strt_dt'
21        ||    ' FROM ws_mkt_core.atlas_account_details'
22        ||    ' CROSS JOIN ws_mkt_dst.tb_tmp_cjs_week_agg_def weeks'
23        ||') accounts '
24    || (SELECT STRING_AGG(' LEFT JOIN ' || table_schema || '.' || table_name || ' ON accounts.analytic_acct_id = ' || table_name || '.analytic_acct_id  and accounts.fisc_wk_strt_dt = ' || table_name || '.fisc_wk_strt_dt ')
25        FROM information_schema.tables
26        WHERE table_schema = 'ws_mkt_dst'
27             AND table_name like 'tb_tmp_b2b_gbl_cjs_%_week_agg')
28    ||' DISTRIBUTED BY (analytic_acct_id,fisc_wk_strt_dt);'
29    ||' GRANT ALL ON ws_mkt_dst.tb_b2b_cjs_tel_week_agg TO ws_grp_mkt_dst_writers;'
30    ||' ALTER TABLE ws_mkt_dst.tb_b2b_cjs_tel_week_agg OWNER TO  ws_grp_mkt_dst_writers;')
31    INTO v_sql_statement;
32    IF v_sql_statement IS NOT NULL THEN
33      EXECUTE v_sql_statement;
34    END IF;
35END $$;