雪花标识符列名

huangapple go评论54阅读模式
英文:

Snowflake identifier column names

问题

我有一个表,其中包含day01、day02和相应的价格。我希望能够根据表中记录的日期动态选择列名。我尝试了一下,但不起作用。我收到了一个语法错误。

create or replace table tableA (
business_date date,
day01 integer,
day02 integer
)

insert into tableA values ('2023-01-01',1,2),('2023-01-02',3,4);

select (identifier(concat('day',right(business_date,2)))),business_date from tableA;
英文:

I have a table that has day01,day02 with prices. I want to be able to select the column name dynamically based on the date from the record in the table. I am trying this but not working. I am getting a syntax error.

create or replace table tableA (
business_date date,
day01 integer,
day02 integer
)

insert into tableA values ('2023-01-01',1,2),('2023-01-02',3,4);

select (identifier(concat('day',right(business_date,2)))),business_date from tableA;

答案1

得分: 0

Using CASE expression:

SELECT business_date, 
       CASE DAY(business_date) 
           WHEN 1 THEN day01
           WHEN 2 THEN day02
       END
FROM tableA;

or DECODE:

SELECT business_date, DECODE(DAY(business_date), 1, day01, 2, day02)
FROM tableA;

"I should be able to use identifier. It works if I do this select (identifier('day01')),business_date from tableA; But I cant get it working dynamically."

select identifier('day01'),business_date from tableA;
-- here 'day01' is constant

set var = 'day01';
select identifier($var),business_date from tableA;
-- variable, but the value after evaluation does not change

It is not possible to provide `IDENTIFIER` expression with column reference because it will have to be evaluated for each row.

-- providing column name explicitly to be used for lookup
create or replace table tableA (
   business_date date,
   day01 integer,
   day02 integer,
   explicit_col_name TEXT
);

insert into tableA
values ('2023-01-01', 1, 2, 'day01'),
       ('2023-01-02', 3, 4, 'day02');

SELECT IDENTIFIER(explicit_col_name)
FROM tableA;
-- Syntax error: unexpected 'explicit_col_name'.
英文:

Using CASE expression:

SELECT business_date, 
      CASE DAY(business_date) 
          WHEN 1 THEN day01
          WHEN 2 THEN day02
       END
FROM tableA;

or DECODE:

SELECT business_date, DECODE(DAY(business_date), 1, day01, 2, day02)
FROM tableA;

> I should be able to use identifier. It works if I do this select (identifier('day01')),business_date from tableA; But I cant get it working dynamically

 select identifier('day01'),business_date from tableA;
 -- here 'day01' is constant

 set var = 'day01';
 select identifier($var),business_date from tableA;
 -- variable, but the value after evaluation does not change

It is not possible to provide IDENTIFIER expression with column reference, because it will have to be evaluated for each row.

-- providing column name explicitly to be used for lookup
create or replace table tableA (
   business_date date,
   day01 integer,
   day02 integer,
   explicit_col_name TEXT
);

insert into tableA
values ('2023-01-01',1,2, 'day01'),
       ('2023-01-02',3,4, 'day02');

SELECT IDENTIFIER(explicit_col_name)
FROM tableA;
-- Syntax error: unexpected 'explicit_col_name'.

huangapple
  • 本文由 发表于 2023年3月10日 01:43:30
  • 转载请务必保留本文链接:https://go.coder-hub.com/75688240.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定