CODEWITHSUNDEEP
sqlamazon-web-servicesamazon-redshift
sujeet_g
sujeet_g

Reputation: 11

Redshift column selection using variable

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?

Upvotes: 1

Views: 1039

Answers (1)

Bill Weiner
Bill Weiner

Reputation: 11032

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.

Upvotes: 1

Related Questions