Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Copy data from 2 tables to 1 table

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Krishnadeva Reddy

New User


Joined: 14 Nov 2007
Posts: 36
Location: Chennai

PostPosted: Thu Nov 21, 2013 12:20 am    Post subject: Copy data from 2 tables to 1 table
Reply with quote

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
View user's profile Send private message

Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1819
Location: NY,USA

PostPosted: Thu Nov 21, 2013 1:27 am    Post subject:
Reply with quote

you can use a full outer join , take the help of below link,

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z10.doc.intro%2Fsrc%2Ftpc%2Fdb2z_fullouterjoin.htm

Code:
load from your cursor name of cursor insert into your final table name


This will get you a better performance than above metod.
Back to top
View user's profile Send private message
Krishnadeva Reddy

New User


Joined: 14 Nov 2007
Posts: 36
Location: Chennai

PostPosted: Thu Nov 21, 2013 3:30 am    Post subject:
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1819
Location: NY,USA

PostPosted: Thu Nov 21, 2013 8:12 am    Post subject: Reply to: Copy data from 2 tables to 1 table
Reply with quote

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. icon_cool.gif
Back to top
View user's profile Send private message
Krishnadeva Reddy

New User


Joined: 14 Nov 2007
Posts: 36
Location: Chennai

PostPosted: Thu Nov 21, 2013 10:08 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1819
Location: NY,USA

PostPosted: Fri Nov 22, 2013 12:41 am    Post subject:
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Extract record for change in combinat... Trinadh DFSORT/ICETOOL 5 Thu Nov 23, 2017 3:32 pm
No new posts Fetching data from JHS as per the cur... arunsoods All Other Mainframe Topics 4 Wed Nov 22, 2017 1:54 pm
No new posts Compare and COPY using DFSORT pshongal DFSORT/ICETOOL 3 Fri Nov 17, 2017 9:49 am
No new posts Extract Record using range of Data scorp_rahul23 DFSORT/ICETOOL 3 Wed Nov 15, 2017 11:54 pm
No new posts Updating data from one file to other!!! Vignesh Sid SYNCSORT 1 Mon Nov 06, 2017 2:42 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us