View previous topic :: View next topic
|
Author |
Message |
Krishnadeva Reddy
New User
Joined: 14 Nov 2007 Posts: 37 Location: Chennai
|
|
|
|
Hello,
Can someone please help me on the below request?
I have 2 old tables (table1 - 4 columns, table2 - 3 columns) and 1 new table (6 columns defined from the above 2 tables). I want to copy the data from old tables to new table using INSERT query. Please see my illustration below.
***Table1
Code: |
ID COLA COLB COLC
--- ------ ------- -------
A123 TEST1 100.00 P3
A124 TEST2 200.00 T1
A126 TEST3 300.00 X2
|
***Table2
Code: |
ID COLD COLE
--- ------ -------
A001 BEN X123
A002 JOHN X123
A123 CHRIS T245
A126 ALLEN K243
|
Output I want to insert in the new table3
***Table3 (New Table)
Code: |
ID COLA COLB COLC COLD COLE
--- ------ ------ ------- ------ -------
A123 TEST1 100.00 P3 CHRIS T245
A124 TEST2 200.00 T1
A126 TEST3 300.00 X2 ALLEN K243
A001 BEN X123
A002 JOHN X123
|
I tried UNION query as shown below but it is not giving correct results.
Code: |
INSERT TABLE3
SELECT X.COLA, X.COLB, X.COLC, Y.COLD, Y.COLE
FROM TABLE1 X,
TABLE2 Y
WHERE X.ID = Y.ID
UNION
SELECT X.COLA, X.COLB, X.COLC, Y.COLD, Y.COLE
FROM TABLE1 X,
TABLE2 Y
WHERE X.ID <> Y.ID
|
|
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3053 Location: NYC,USA
|
|
Back to top |
|
|
Krishnadeva Reddy
New User
Joined: 14 Nov 2007 Posts: 37 Location: Chennai
|
|
|
|
Thanks Rohit.
I tried using FULL OUTER JOIN but I'm not getting complete data loaded in the new table. The table2 contains value as '0' in ID field and If I want to exclude those records, I'm not getting data from table1 loaded into table3.
**Table1
Code: |
ID COLA COLB COLC
--- ------ ------- -------
A123 TEST1 100.00 P3
A124 TEST2 200.00 T1
A126 TEST3 300.00 X2
|
**Table2
Code: |
ID COLD COLE
--- ------ -------
0 IAN D356
0 CAR C123
A001 BEN X123
A002 JOHN X123
A123 CHRIS T245
A126 ALLEN K243
|
I have used below query:
Code: |
SELECT COALESCE(X.ID,Y.ID) AS ID,
X.COLA,X.COLB,X.COLC,
Y.COLD,Y.COLE
FROM TABLE1 X
FULL OUTER JOIN
FROM TABLE2 Y
ON X.ID = Y.ID
WHERE Y.ID > 0
|
The output I'm seeing: A124 record is missing from Table1
Code: |
ID COLA COLB COLC COLD COLE
--- ------ ------ ------- ------ -------
A123 TEST1 100.00 P3 CHRIS T245
A126 TEST3 300.00 X2 ALLEN K243
A001 BEN X123
A002 JOHN X123
|
Any thoughts please. |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3053 Location: NYC,USA
|
|
|
|
Please correct the syntax of full outer join..you have an additional FROM condition. Also instead of using WHERE condition you can add the same using AND in outer join condition. |
|
Back to top |
|
|
Krishnadeva Reddy
New User
Joined: 14 Nov 2007 Posts: 37 Location: Chennai
|
|
|
|
I'm seeing SQLCODE = -338, ERROR: AN ON CLAUSE IS INVALID
if I remove WHERE clause and add "AND" in outer join condition.
"An operator other than '=' is not allowed in a FULL OUTER JOIN or FULL
JOIN."
Code: |
SELECT COALESCE(X.ID,Y.ID) AS ID,
X.COLA,X.COLB,X.COLC,
Y.COLD,Y.COLE
FROM TABLE1 X
FULL OUTER JOIN
TABLE2 Y
ON X.ID = Y.ID
AND Y.ID > 0
;
|
|
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3053 Location: NYC,USA
|
|
|
|
Please try below query and let us know
Code: |
SELECT COALESCE(X.ID,Y.ID) AS ID,
X.COLA,X.COLB,X.COLC,
Y.COLD,Y.COLE
FROM TABLE1 X
FULL OUTER JOIN
(SELECT ID,COLD,COLE FROM TABLE2
WHERE ID >0) AS Y
ON X.ID = Y.ID |
|
|
Back to top |
|
|
|