Insert Statement Within A Case Statement

[ianmcdonagh]

Hi folks,

I'm looking to pick everyone's brains.

I have a cursor, with over 200 columns in the select.

I am using a case in one of the columns which I'm retrieving, and want to issue an insert statement within this. i know people will come back with different merge or whatever, but i must be able to do this within a case statement unfortunately.

example

CURSOR c_student_data IS
SELECT a,
b,
CASE WHEN c = 1
THEN 'ONE'
WHEN c = 2
THEN 'TWO'
WHEN c = 3
THEN -- do 2 things if possible, display value as zz, and insert into another table.
'zz',
INSERT INTO error_table values(x,y,z)
END
d,
e
FROM t_table;

Now, i've spent quite a bit of time trying all different versions of this, can anyone shed some light on this please as I've not actually seen this being done before and can't find anything on the net regarding it.

Thanks in advance.

[debasisdas]

you need to separate cursor fro the insert statment. insert statment can't be part of cursor.

check value in curson and based on that go for insert operation.

[amitpatel66]

You cannot use an INSERT statement as a part of CASE WHEN in SELECT query. You can try something like this:

[code=oracle]

INSERT INTO table_name SELECT (CASE WHEN 1 = 1 THEN 1 ELSE 2 END),column_2,c olumn_3,column_ 4,column_5 FROM table_name WHERE (CASE WHEN 1 = 1 THEN 1 ELSE 2 END) = column_2;

[/code]