SELECT multiple columns, stored in a table

Question

I have business rules applied in CASE statement of multiple stored procedure. Business rules have on going changes so I want to separate them out and store in a table. This will help to make changes in one place and no need to update multiple stored procedure.

How can I embed columns, stored in different table, in SELECT statement?

CREATE OR REPLACE TABLE stage_data 
(
    event_date    DATE,
    website       VARCHAR,
    event_type    VARCHAR,
    event_action  VARCHAR,
    index         VARCHAR,
    index_value   VARCHAR
);

INSERT INTO stage_data
VALUES
    ('2023-07-01', 'website_1', 'event', 'form_success',       null, null),
    ('2023-07-01', 'website_2', 'page',  'hoursanddirections', 3,    null),
    ('2023-07-01', 'website_3', 'page',  'vehicledetails',     3,    null),
    ('2023-07-01', 'website_4', 'event', 'click_to_call',      6,    'sale'),
    ('2023-07-01', 'website_5', 'event', 'click_to_call',      6,    'service');
    
SELECT 
    event_date,
    website,
    CASE 
        WHEN event_type = 'event' AND event_action = 'form_success'                                              
            THEN 'Y' 
            ELSE 'N' 
    END AS lead_events,
    CASE
        WHEN event_type = 'page' AND event_action = 'hoursanddirections' AND index = 3                           
            THEN 'Y' 
            ELSE 'N' 
    END AS hd_views,
    CASE
        WHEN event_type = 'page' AND event_action = 'vehicledetails' AND index = 3
            THEN 'Y' 
            ELSE 'N' 
    END AS vdp_views,
    CASE
        WHEN event_type = 'event' AND event_action = 'click_to_call' 
             AND index = 6 AND index_value = 'sale'    
            THEN 'Y' 
            ELSE 'N' 
    END AS ctc_sales,
    CASE 
        WHEN event_type = 'event' AND event_action = 'click_to_call'      
             AND index = 6 AND index_value = 'service' 
            THEN 'Y' 
            ELSE 'N' 
    END AS ctc_services
FROM 
    stage_data
ORDER BY 
    1, 2, 3, 4, 5, 6;

Now I want to store that case statements in a different table (stage_business_rules) and use that as columns in my original table (stage_data).

CREATE OR REPLACE TABLE stage_business_rules 
(
    businesss_rules  VARCHAR
);

INSERT INTO stage_business_rules
VALUES 
('case when event_type=''event'' and event_action=''form_success'' then ''Y'' else ''N'' end AS lead_events'),
('case when event_type=''page'' and event_action=''hoursanddirections'' and index=3 then ''Y'' else ''N'' end AS hd_views'),
('case when event_type=''page'' and event_action=''vehicledetails''  and index=3 then ''Y'' else ''N'' end AS vdp_views'),
('case when event_type=''event'' and event_action=''click_to_call'' and index=6 and index_value=''sale'' then ''Y'' else ''N'' end AS ctc_sales'),
('case when event_type=''event'' and event_action=''click_to_call'' and index=6 and index_value=''service'' then ''Y'' else ''N'' end AS ctc_services');

SELECT 
    event_date
  , website 
  , (SELECT businesss_rules FROM stage_business_rules) -- This needs to be figure out?
FROM stage_data;

Answer 1

So the first task is to build some SQL that builds the SQL you would like:

SELECT businesss_rules FROM stage_business_rules

is a good start:

if we use LISTAGG you can build a single string comma separated

SELECT
    listagg(businesss_rules, ', ') as code 
FROM stage_business_rules;

so given the prefix code and suffix code you want, and given there might be no rules, it might be easier to prefix each line with the comma, and join with the newline:

SELECT 
    'SELECT\r\n event_date\r\n, website\r\n' ||
    listagg('  ,' || businesss_rules,'\r\n') ||
    '\r\nFROM stage_data;' as sql
FROM stage_business_rules;

SELECT
 event_date
, website
  ,case when event_type='event' and event_action='form_success' then 'Y' else 'N' end AS lead_events
  ,case when event_type='page' and event_action='hoursanddirections' and index=3 then 'Y' else 'N' end AS hd_views
  ,case when event_type='page' and event_action='vehicledetails'  and index=3 then 'Y' else 'N' end AS vdp_views
  ,case when event_type='event' and event_action='click_to_call' and index=6 and index_value='sale' then 'Y' else 'N' end AS ctc_sales
  ,case when event_type='event' and event_action='click_to_call' and index=6 and index_value='service' then 'Y' else 'N' end AS ctc_services
FROM stage_data;

So now we have the SQL built, you might want to execute it, so using Snowflake Scripting we can wrap this in a block, assign this to a variable, and run it:

EXECUTE IMMEDIATE $$
DECLARE
    sql TEXT;
BEGIN
    sql := (SELECT 
    'SELECT\r\n event_date\r\n, website\r\n' ||
    listagg('  ,' || businesss_rules,'\r\n') ||
    '\r\nFROM stage_data;' as sql
FROM stage_business_rules);

    return sql;
END;
$$;

right, now this return the SQL we want to run (checking done, so we know we have to correct), we can swap it to execute inside the block:

EXECUTE IMMEDIATE $$
DECLARE
    sql TEXT;
BEGIN
    sql := (SELECT 
    'SELECT\r\n event_date\r\n, website\r\n' ||
    listagg('  ,' || businesss_rules,'\r\n') ||
    '\r\nFROM stage_data;' as sql
FROM stage_business_rules);

    EXECUTE IMMEDIATE sql;
END;
$$;

and I don't have a STAGE_DATA table, so I get and error message ;-P

002003 (42S02): Uncaught exception of type 'STATEMENT_ERROR' on line 11 at ? position 4 : SQL compilation error: Object 'STAGE_DATA' does not exist or not authorized.