1

How to use SELECT Sub-Query in alias for a column?

Here's my script:-

/*Declaring variables:*/
SET period= '3';
SET smryseg=concat('sku',$period,'_smry');
SET spend= concat('sku',$period,'')
/*Printing it:*/
SELECT $period;                   /* #O/P: 3 */
SELECT $smryseg;                  /* #O/P: sku3_smry */
SELECT $spend;                    /* #O/P: sku3_spend */

/*now I want to use this variable in my INNER SELECT query:*/

create table IDENTIFIER ($smryseg) as
SELECT sum(spend) as (SELECT $spend)
FROM my_table;

Here, the last query is giving me an error, I also tried using IDENTIFIER, CONCAT, SUBSRING, $ ,removing parenthesis and much more. I just want the name of column 'sum(spend)' obtained as 'sku3_spend' i.e in dynamic format

Aviraj Korde
  • 11
  • 1
  • Take a closer look at your definition for $spend. While the comments show that its value is "sku3_spend", the definition actually sets it to "sku3". Your concat has a blank string at the end. Is the column name actually "sku3_spend"? – Greg Pavlik Mar 08 '20 at 01:04

1 Answers1

0

You can rename the column afterwards. Here's a self-contained example:

set spend_col='sku3_spend';
create or replace table t as select sum(spend) as x from values(1),(2),(3) s(spend);
alter table t rename column x to identifier($spend_col);

UPDATE

You can store dynamically named columns in an object:

set spend_col='sku3_spend';

create or replace table t as 
    select object_construct($spend_col, sum(spend)) data 
    from values(1),(2),(3) s(spend);

select data:sku3_spend from t;
waldente
  • 1,324
  • 9
  • 12
  • I already tried this and it works too, but what if I only have select access to it? Also I don't want to create another table which I already did. BTW Thanks. – Aviraj Korde Mar 05 '20 at 07:30
  • Hmm. Can you do `select sum(spend) as value, $spend as label ...`? Or dynamic SQL. That's all I got. – waldente Mar 05 '20 at 14:20
  • Actually one more idea -- Store variably named columns in an OBJECT column. I added an example to the answer. Hope that helps. – waldente Mar 05 '20 at 15:22