在values()子句中使用CASE WHEN插入新表- Snowflake SQL(经典web界面)

Question

我试图使用insert into()和values()基于现有条件在我的原始表创建一个新表。我确实有使用insert into()的工作代码以及select和where子句，但我想看看是否有可能在值之外做一个CASE WHEN语句，以便根据条件将某些值插入到新表中。

-- Creation and inserting values into table invoice_original
create temporary table invoice_original (id integer, price number(12,2),
purpose varchar);
insert into invoice_original (id, price, purpose) values
(1, 11.11, 'Business'),
(2, 22.22, 'Personal'),
(3, 33.33, 'Business'),
(4, 44.44, 'Personal'),
(5, 55.55, 'Business');


--  Creates final empty table invoice_final
create temporary table invoice_final (
study_number varchar,
price number(12, 2),
price_type varchar
);

代码:

execute immediate $$
declare
new_price number(12,2);
new_purpose varchar;
c1 cursor for select price, purpose from invoice_original;
begin
for record in c1 do
new_price := record.price;
new_purpose := record.purpose;

-- This code runs!                
insert into invoice_final(study_number, price, price_type)
select 1, :new_price, 'Dollars'
where :new_purpose ilike '%Business%';
insert into invoice_final(study_number, price, price_type)
select 2, :new_price, 'Dollars'
where :new_purpose not like '%Business%';

-- Does not run but this is what I'm trying to do instead  
CASE  
WHEN :new_purpose ilike '%Business%' then 
insert into invoice_final(study_number, price, price_type) 
values('1', :new_price, 'Dollars')
ELSE 
insert into invoice_final(study_number, price, price_type) 
values('2', :new_price, 'Dollars') END

end for;
end;
$$;

这只是我试图做的一个简化的例子，但只是想知道在这种情况下插入()值()是否可能。

Answer 1

您需要使用INSERT ALL。参考这里。

更改代码如下

execute immediate $$
declare
new_price number(12,2);
new_purpose varchar;
c1 cursor for select price, purpose from invoice_original;
begin
for record in c1 do
new_price := record.price;
new_purpose := record.purpose;
INSERT ALL
when npurpose ilike '%Business%' then
into invoice_final(study_number, price, price_type)
values ('1',nprice,'Dollars')
else
into invoice_final(study_number, price, price_type)
values('2',nprice,'Dollars')
select :new_price as nprice, :new_purpose as npurpose;
end for;
end;
$$;

执行上述命令将产生以下结果-

select * from INVOICE_FINAL;

tbody> <<tr>2121

STUDY_NUMBER	PRICE	PRICE_TYPE
1	11	美元
	22	美元
	33	美元
	44	美元
	56	美元

允许CASE语句作为分支构造:

变化:

a) using INSERT INTO SELECT

b)每个语句必须结束与;

execute immediate $$
declare
new_price number(12,2);
new_purpose varchar;
c1 cursor for select price, purpose from invoice_original;
begin
for record in c1 do
new_price := record.price;
new_purpose := record.purpose;

CASE  
WHEN :new_purpose ILIKE'%Business%' THEN
INSERT INTO invoice_final(study_number, price, price_type) 
SELECT '1', :new_price, 'Dollars';
ELSE 
INSERT INTO invoice_final(study_number, price, price_type) 
SELECT '2', :new_price, 'Dollars';
END CASE;

end for;
end;
$$;

免责声明:当无法将代码重写为基于集合的方法时，一般应使用游标循环和循环。

INSERT INTO invoce_final(study_number, price, price_type)
SELECT CASE WHEN purpose ILIKE'%Business%' THEN 1 ELSE 2 END,
price,
'Dollars'
FROM invoice_orginal;

我很快就会写一些示例代码。

但是形式

INSERT INTO (SELECT FROM VALUES WHERE)

允许显示所有的N值行。CASE逻辑在WHERE中，结果SUB-SELECT为insert。

如果您在Snowflake Scripting中，另一个选择是在这些insert周围使用if而不是使用CASE

我的意思是，用"完整的SQL变量"作为我的例子，不是循环的一部分。是你可以像这样移动SQL语句，并有一个块:

create temporary table invoice_final (
study_number varchar,
price number(12, 2),
price_type varchar
);

set new_purpose = 'Business';
set new_price = 10.0::number(12, 2);

insert into invoice_final(study_number, price, price_type)
SELECT column1, column2, column3
FROM VALUES 
('1', $new_price, 'Dollars'),
('2', $new_price, 'Dollars')
WHERE CASE WHEN $new_purpose ilike '%Business%' then '1' ELSE '2' END = column1;       

因此，对于您的循环，三个$将替换为:，但这只插入一行。

IF的意思是雪花脚本IF，但是下面也有使用CASE的例子。