Geek Answers Handbook

INSERT INTO in CASE statement

I am wondering if it is possible to have an INSERT INTO statement in a CASE statement in SQL code.

Here is a crude pseudo-code 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
+7
source share
2 answers

Instead, you need to execute the IF...THEN commands. Something like this (not sure about the 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;
+9
source

You can do this with two statements, for example:

Insert 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 will be 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

I guess you really need to change the connections 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 the equivalent of DB2 Coalesce

0
source

More articles:


All Articles