Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups 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: 1609
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: 1609
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: 1609
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 Is the 'prompt' data for the DSLIST p... Willy Jensen TSO/ISPF 0 Tue Dec 06, 2016 4:38 am
No new posts How to move a long alphanumeric data ... lind sh COBOL Programming 5 Mon Dec 05, 2016 7:51 pm
No new posts syncsort: copy lines after the keyword shreya19 SYNCSORT 3 Fri Dec 02, 2016 9:47 am
No new posts ODPP(Optim Data privacy Provider) Iss... Rama kishore IBM Tools 1 Mon Nov 07, 2016 5:46 pm
No new posts Can sending 5 MB data between cobol p... Kevin Vaz CICS 12 Tue Oct 18, 2016 4:50 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us