Concatenate and join two tables with different numbers. columns and column name
I tried to concatenate the fus_shift and root tables into a new table, the final table , but it displays as " ERROR on line 2: ORA-01789: Query block has wrong number of result columns . I tried to concatenate the table as my alternative, but also output" ERROR on line 3: ORA-00918: column is ambiguous "Is there any other way to join and join two tables with different number of columns and have the same column name respectively? Thanks again :-)
code error:
create final table as
select * from fus_shift
concatenation
select * from root,
code error:
select record_num, test_num, t_date, t_time, system_type, category, comments, val
from fus_shiftrpt, root
where record_num = record_num;
my tables:
fus_shift Table
Record_Num test date time system
-----------------------------------------------------------
1 test15 08-11-12 13:20:01 sys23
2 test17 08-11-03 14:24:00 sys24
3 test13 08-11-13 17:25:04 sys45
4 test15 08-11-14 18:24:00 sys67
5 test16 08-11-15 19:24:06 sys45
root Table
Record_Num category comments validated by
---------------------------------------------------
1 dirt checked admin
2 prog checked admin
3 dirt checked pe
4 wires checked ee
5 prog repair admin
underlined text
source to share
You certainly cannot apply Union to your tables. It can only be used if both queries return the same number (and of the same type) of columns.
You can join two tables, but you must use the "table alias" when joining, since the "record_num" field is common in both tables. Here is a query that will work for you
select
table1.record_num,
table1.test_num,
table1.t_date,
table1.t_time,
table1.system_type,
table2.category,
table2.comments,
table2.val
from
fus_shift table1,root table2
where
table1.record_num = table2.record_num;
source to share