user2217874 user2217874 - 1 month ago 6
SQL Question

Merging two tables into one in Oracle SQL

I have two tables, each with many attributes. There are attributes that I want from each, and I am trying to write a query to get those attributes, and put them into three columns.

Table A

LOCATION_NUMBER (varchar2(20Byte)),
BEGIN (NUMBER (3,0)),
BEGIN_DEC (NUMBER (4,3)),
END (NUMBER (3,0)),
END_DEC (NUMBER (4,3))


Table B

LOCNUM (NUMBER (15,0)),
START (VARCHAR2(6 BYTE)),
START_POS (NUMBER (5,3)),
FINISH (VARCHAR2 (6 BYTE)),
FINISH_POS (NUMBER (5,3)).


What I want from the query is one table, with three attributes and then order these tables by the location first, and start second.

Table C,

LOCATION,
START,
END


Here's an example:

A:

LOCATION_NUMBER BEGIN BEGIN_DEC END END_DEC
I 26 8 0.63 13 0.99
I 32 144 0 151 0.82
I 35 22 0.29 55 0.09
I 35 128 0.1 67 0.9
I 35 0 0 22 0.28


B:

LOC_NUM START START_POS FINISH FINSIH_POS
227 98 0.56 100 0
35 149 0.25 151 0.55
96 156 0.23 156 1.26
11 263 2.56 263 4.52


RESULT SET:

LOCATION START END
26 8.63 13.99
32 144 151.82
35 22.29 55.09
35 128.1 67.9
35 0 22.28
227 98.56 100
35 149.25 151.55
96 156.23 157.26
11 265.56 267.52


I have some queries that I tried I am able to get the desired results from each table, I just need to know how to combine them into one to make things easier for what I want the table for, again, each of the tables have 10 - 20 more attributes that I don't want to worry about.

Select to_number(trim(regexp_substr(A.LOCATION_NUMBER ,'[^ ]+', 1, 2))) AS LOCATION,
(to_number(trim(A.BEGIN)) + to_number(trim(A.BEGIN_DEC))) AS START,
(to_number(trim(A.END)) + to_number(trim(A.END_DEC))) AS END
FROM A
ORDER BY to_number(trim(regexp_substr(A.LOCATION_NUMBER,'[^ ]+', 1, 2)));

SELECT B.LOC_NUM AS LOCATION,
(to_number(trim(B.START)) + to_number(trim(B.START_POS))) AS START,
(to_number(trim(B.FINISH)) + to_number(trim(B.FINISH_POS))) AS END
FROM B
ORDER BY B.LOCATION, START;

Answer

Assuming your queries work, you just want union all:

Select to_number(trim(regexp_substr(A.LOCATION_NUMBER ,'[^ ]+', 1, 2))) AS LOCATION,
       to_number(trim(A.BEGIN)) + to_number(trim(A.BEGIN_DEC)) AS START,
       to_number(trim(A.END)) + to_number(trim(A.END_DEC)) AS END
FROM A
UNION ALL
SELECT B.LOC_NUM AS LOCATION,
       to_number(trim(B.START)) + to_number(trim(B.START_POS)) AS START,
       to_number(trim(B.FINISH)) + to_number(trim(B.FINISH_POS)) AS END
FROM B
ORDER BY B.LOCATION, START;
Comments