英文:
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'.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论