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..
精彩评论