Selecting Column based on variable passed

JanGLi

Hi,

This is my query:

insert into [email protected]

(

process_status,

organization_code,

plan_name,

decode(qa_results_stage.mon,'Jan',qa.character14,'Feb',qa.character15,NULL)

)

values (select

3,

'FFL',

'HYPERION_CAPEX_WB',

MON

from qa_results_stage

where product = 'CAPEX Additions');

I am passing variable (month name like Jan and Feb) passed on the variable passed i have to select a the table in which i have to insert data. I am trying to achieve this using decode but i am unable to do so.

Need expert opinion.

Regards

Hans Steijntjes

If I get it right the target table name depends on the variable passed.

If you are on Oracle 12c you could use an multi-table conditional insert (INSERT ALL).

Something like:

INSERT ALL

WHEN 'Jan' = '&month' THEN

INSERT tab_jan VALUES(.....)

WHEN 'Feb' = '&month' THEN

INSERT tab_feb VALUES(...)

SELECT ... FROM ... (yourquery).

See http://www.oracletutorial.com/oracle-basics/oracle-insert-all/ or the SQL reference guide.

Edit: if the conditions are mutually exclusive (which they seem to be) you can better use 'INSERT FIRST'.

Frank Kulash

Hi,

JanGLi wrote:Hi,
This is my query:insert into [email protected](process_status,organization_code,plan_name,decode(qa_results_stage.mon,'Jan',qa.character14,'Feb',qa.character15,NULL))values (select3,'FFL','HYPERION_CAPEX_WB',MONfrom qa_results_stagewhere product = 'CAPEX Additions');

Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved, so that the people who want to help you can re-create the problem and test their ideas.
Also post the exact results you want from that data, and an explanation of how you get those results from that data, with specific examples.
If you're asking about a DML statement, such as INSERT, then the CREATE TABLE and INSERT statements you post should re-create the tables as they are before the DML, and the results  will be the contents of the changed table(s) when everything is finished.

Always say which version of Oracle you're using (for example, 12.2.0.1.0).
See the forum FAQ:

I am passing variable (month name like Jan and Feb) passed on the variable passed i have to select a the table in which i have to insert data. 

In the thread title, and also in the code above, the column was variable.

Now you're saying the table is variable.

Which is it?  Or are both the column and the table variable?  This  shows one of the many reasons why you need to post sample data and desired results: to help explain what you want.

I am trying to achieve this using decode but i am unable to do so.

DECODE returns an expression.  Column names and table names can't be expressions; they have to be hard-coded.

DECODE (or CASE) can be used in a statement where multiple tables and/or multiple columns are referenced, but the DECODE guarantees that only one of them is actually used.  For example, if you want to INSERT an empno and EITHER a sal or a comm value into the emp table:

INSERT INTO emp (empno, sal, comm)VALUES ( :new_empno       , CASE             WHEN  :column_to_insert = 'SAL'             THEN  :new_sal_or_comm         END       , CASE             WHEN  :column_to_insert = 'COMM'             THEN  :new_sal_or_comm         END       );

This INSERT statement always INSERTs  both sal and comm, but the CASE expressions guarantee that one of those columns will always be NULL.