Q
Problem solve Get help with specific problems with your technologies, process and projects.

Inserting three columns into one

I have three columns (NUM1, NUM2, NUM3) of "number" datatype in table 1, and while inserting to table2 using "insert into table2 select * from table1", I want to insert the three columns into one column(NUM).

I'm using Oracle 8i, and only one of the three columns will have a value in a row, while others will have null values.


I understand from your question that only one and exactly one number from the three numbers will be filled, and the others will stay null. If this is the case, do a:

   Insert into table2   (f1,f2,........fk)
  select v1,  v2,   .....vk
from table1;
where table2 has the num field. Insert a corresponding DECODE expression that does the following:
DECODE ( NVL (NUM1 ,3) / NVL(NUM1,1)  *100 +  NVL(NUM2,5) / NVL(NUM2,1) *10+ 
  NVL(NUM3,7)/ NVL(NUM3,1) ,
               157,NUM1,317,NUM2,351,NUM3)
This expression is designed to identify which column to return.

For More Information


Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close