INSERT INTO in a CASE statement

Question

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

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

score 10 · Accepted Answer · answered May 13 '11 at 19:09

10

You will need to accomplish with IF...THEN statements instead. Something roughly like this (not sure about syntax for 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;

answered May 13 '11 at 19:09

mellamokb

56,094
12
110
136

1

That's the problem. I'm not sure how to replicate something like that in DB2 – Connection May 13 '11 at 19:21
Um.. Start with the documentation?? I've given you a plausible structure for your code to do what you're trying to do. You can dig into the specifics of the exact syntax. I know this structure would work in SQL Server or MySql. – mellamokb May 13 '11 at 19:22

score 0 · Answer 2 · answered May 13 '11 at 19:28

You could do it two statements like so.

First insert into other when somevalue is null

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

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

INSERT INTO in a CASE statement

2 Answers2

Linked