开发者

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_C000000000开发者_StackOverflow社区400003

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?


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;


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..

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜