Redshift column selection using variable

user_17097502 0
Viewed 225

I need to select some columns from a Amazon Redshift table by storing column_name into a variable using query:-

WITH tmp_variables AS (
SELECT     
   'Number'      AS filter_value,
   'required_field2' AS colname1,
   'required_field'       AS colname2    
)
SELECT DISTINCT (SELECT colname1 FROM tmp_variables), (SELECT colname2 FROM tmp_variables)
FROM table_abc where filter_column = (SELECT filter_value FROM tmp_variables);

Here the filter condition is working fine but this is not returning the data as a normal select query gives if I use the variable values directly. What do I need to change here?

amazon-redshift amazon-web-services sql
1 Answers

This cannot work (as written). Redshift compiles the query BEFORE any data is read so using data as part of the query cannot work.

If you want to do this you could make a stored procedure that reads the tmp_variables table and then issues a query based on this input. Or you can use some external control layer that reads the tmp_variables layer and issues the desired query. This could be a Lambda or bash code or any number of industry tools ... I recommend the external path as it is more flexible and saves Redshift for processing large data instead of running management queries.

user_133506520
Related