在CASE声明中插入INTO

I'm wondering if it's possible to have a INSERT INTO statement within a CASE statement in SQL code.

我想知道是否可以在SQL代码的CASE语句中使用INSERT INTO语句。

Here's a rough pseudocode of what I'm trying to do:

这是我正在尝试做的粗略伪代码:

SELECT (CASE (SELECT SomeValue FROM SomeTable)
            WHEN NULL THEN
                INSERT INTO OtherTable VALUES (1, 2, 3)
                (SELECT NewlyInsertedValue FROM OtherTable)
            ELSE
                (SELECT SomeOtherValue FROM WeirdTable)
        END),
       Column1,
       Column2
FROM BigTable

2 个解决方案

#1

You will need to accomplish with IF...THEN statements instead. Something roughly like this (not sure about syntax for db2):

您需要完成IF ... THEN语句。大致相似的东西(不确定db2的语法):

SELECT @SomeValue = SomeValue FROM SomeTable

IF @SomeValue IS NULL
    INSERT INTO OtherTable VALUES (1, 2, 3)
    SELECT NewlyInsertedValue FROM OtherTable;
ELSE
    INSERT INTO OtherTable VALUES (1, 2, 3)
    SELECT SomeOtherValue FROM WeirdTable;
END IF;

#2

You could do it two statements like so.

你可以这样做两个语句。

First insert into other when somevalue is null

当somevalue为null时,首先插入到other中

INSERT INTO othertable 
SELECT 1, 
       2, 
       3 
FROM   bigtable 
WHERE  somevalue IS NULL;

Then left join to both tables on Somevalue being null or not null

然后左边连接到Somevalue上的两个表为null或不为null

SELECT Coalesce(othertable.newlyinsertedvalue, weirdtable.someothervalue) foo, 
       column1, 
       column2 
FROM   bigtable 
       LEFT OUTER JOIN othertable 
         ON somevalue IS NULL 
       LEFT OUTER JOIN weirdtable 
         ON somevalue IS NOT NULL

My guess is that you will actually have to modify the joins to be something like

我的猜测是你实际上必须修改连接类似的东西

       LEFT OUTER JOIN othertable 
         ON somevalue IS NULL 
           and bigtable.id = othertable.id
       LEFT OUTER JOIN weirdtable 
         ON somevalue IS NOT NULL 
           and bigtable.id = weirdtable .id

Note: I'm not sure what the DB2 equivalent of Coalesce is

注意:我不确定Coalesce的DB2等价物是什么

#1