1

I have a UNLOAD query in Redshift where I need the file name to be generated dynamically like 'Filename.year.quarter' and I am passing a query to derive year and quarter like below: 

UNLOAD('Query1') 
TO  '::P1::/test/test_folder'
     || (select ent_qtr 
         from db.w_day_d 
         where calendar_date=to_char(sysdate, 'yyyy-mm-dd 00:00:00')) 
     || (select ent_year 
         from db.w_day_d 
         where calendar_date=to_char(sysdate, 'yyyy-mm-dd 00:00:00')) 
CREDENTIALS 'aws_access_key_id=::P2::;aws_secret_access_key=::P3::' ADDQUOTES
DELIMITER as ',' ALLOWOVERWRITE NULL as ''  HEADER PARALLEL OFF;

But this query is not allowing me to use || operator too. Can anyone please suggest a better way in deriving a filename like 'Filename.year.quarter' ?

Thanks

Prajakta Yerpude
  • 215
  • 6
  • 20
  • why dont use `SELECT .... INTO @variable` first https://stackoverflow.com/questions/12328198/store-query-result-in-a-variable-using-in-pl-pgsql – Juan Carlos Oropeza Sep 26 '19 at 15:47
  • BTW You can find the year and quarter in a single query. `select ent_year || '.' || ent_qtr INTO file_name` . And year should be first. – Juan Carlos Oropeza Sep 26 '19 at 15:49
  • Hi, thanks for the response. But I wanted something the filename like UNLOAD('Query1') TO '::P1::/test/test_folder_'|| ent_year || '.Q' || ent_qtr from db.w_day_d where calendar_date=to_char(sysdate, 'yyyy-mm-dd 00:00:00') where the filename generated would be test_folder_2019.Q4 – Prajakta Yerpude Sep 30 '19 at 16:24
  • That is what I said. Use `SELECT .... INTO @variable` and then `UNLOAD('Query1') TO || @variable` – Juan Carlos Oropeza Sep 30 '19 at 19:18

0 Answers0