CODEWITHSUNDEEP
sqloracle-database
reforrer
reforrer

Reputation: 745

Oracle given column names

I have 2 tables with 2 columns (user_id and year).

Query1:

SELECT * FROM table_1 t1 
FULL JOIN table_2 t2 ON t1.user_id=t2.user_id AND t1.year=t2.year

Produces following column names:

user_id, year, user_id_1, year_1

Query2:

CREATE TABLE table_copy AS SELECT * FROM
(SELECT * FROM table_1 t1 
 FULL JOIN table_2 t2 ON t1.user_id=t2.user_id  AND t1.year=t2.year);

Produces following vague column names:

QCSJ_C000000000400000, QCSJ_C000000000400002, QCSJ_C000000000400001, QCSJ_C000000000400003

Is there a short way to force Oracle query2 to use the same names as query1 without writing them explicitly (it is important when there are many columns)? Maybe some Oracle settings?

Upvotes: 3

Views: 1172

Answers (2)

pratik garg
pratik garg

Reputation: 3342

I didn't fully understand your expected part but what I understood is you want all columns from table_1 or from table_2 only

if it is like this only you can use following query to create table ..

CREATE TABLE table_copy AS SELECT * FROM 
    (SELECT t1.* FROM table_1 t1 FULL JOIN table_2 t2 
      ON t1.user_id=t2.user_id  
      AND t1.year=t2.year);

or if you want both table's column but with different name in sql then you have to follow query suggested by cagcowboy only....

but you can create table with prefix like "t1_" in plsql without specify or write all column name..

Upvotes: 0

cagcowboy
cagcowboy

Reputation: 30898

List your columns and use AS to specify the column name.

e.g.

CREATE TABLE table_copy AS
SELECT t1.user_id AS t1_user_id,
       t1.year    AS t1_year,
       t2.user_id AS t2_user_id,
       t2.year    AS t2_year
FROM   table_1 t1
FULL   JOIN table_2 t2 ON t1.user_id=t2.user_id
AND    t1.year=t2.year;

Upvotes: 5

Related Questions