Connection Connection - 3 months ago 27
SQL Question

INSERT INTO in a CASE statement

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

Answer

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;