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
 

 

Comparing two tables

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Cketha
Warnings : 1

New User


Joined: 19 May 2007
Posts: 22
Location: mumbai

PostPosted: Fri Feb 08, 2008 7:28 pm    Post subject: Comparing two tables
Reply with quote

Hi,

We have to compare two tables F1,F2 on pin_cd,pin_nm key.If a particuar record is present in the table F1 and not present in the F2 that particular record have to be deleted from the F2.Please refer to the below example.

F1
---
Pin_cd Pin_nm Pin_Desc
------- ------- -----------
01 acd past
02 mmm future
03 kcd present

F2
--
Pin_cd Pin_nm Pin_Desc
------- ------- -----------
01 acd past
02 mmm future
05 aaa present

In the above example 05 record has to be deleted from F2.
WE SHOLUD NOT USE ANY SUB QUERIES FOR COMPARING

Thanks
Back to top
View user's profile Send private message

enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10274
Location: italy

PostPosted: Fri Feb 08, 2008 7:35 pm    Post subject: Reply to: Comparing two tables
Reply with quote

Quote:
WE SHOLUD NOT USE ANY SUB QUERIES FOR COMPARING

why not ??

is this some kind of homework
that' s what comes to my mind when somebody asks for a solution the way You did
( yelling on what should not be used )

if You have a job to be done You should have the freedom of choosing the
most proper way of doing it...

in terms of...
time for development
ease of debugging
ease of maintenance
resource utilization
overall performance
Back to top
View user's profile Send private message
Cketha
Warnings : 1

New User


Joined: 19 May 2007
Posts: 22
Location: mumbai

PostPosted: Fri Feb 08, 2008 7:49 pm    Post subject:
Reply with quote

Hi,

We have done it already thru the sub queries ,but we were given a task to do it thru the join without using sub queries ,inorder to know which process is efficient interms of cost.
Back to top
View user's profile Send private message
Prajesh_v_p

Active User


Joined: 24 May 2006
Posts: 133
Location: India

PostPosted: Mon Feb 11, 2008 4:36 pm    Post subject:
Reply with quote

If you have used a correlated subquery.. somthing like
Delete from F2 T1
where not exists ( select 1 from f1 T2
T2.Pin_cd = T1.Pin_cd
T2.pin_nm = T1.pin_nm ) ;.. Then I feel it is good a solution. Also you cannot use join directly on a delete query as the delete operation applies only one table.

Hope this helps.
Back to top
View user's profile Send private message
vsgaikwad

New User


Joined: 20 Nov 2007
Posts: 6
Location: Pune

PostPosted: Fri Mar 07, 2008 8:29 am    Post subject:
Reply with quote

Hi Prajesh,

I tried your code but it's not working.

SQLCODE = -104, ERROR: ILLEGAL SYMBOL "T1". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: UNION
SQLSTATE = 42601 SQLSTATE RETURN CODE


Can u clarify what is missing here.
Back to top
View user's profile Send private message
vsgaikwad

New User


Joined: 20 Nov 2007
Posts: 6
Location: Pune

PostPosted: Fri Mar 07, 2008 9:03 am    Post subject:
Reply with quote

Hi,
I tried following & now it's working fine.

Code:
DELETE  FROM A                               
  WHERE NOT EXISTS (                                     
                              SELECT * FROM B
WHERE  B.EMP_NO = A.EMP_NO);     


Thanks,

Vikas G.
Back to top
View user's profile Send private message
Prajesh_v_p

Active User


Joined: 24 May 2006
Posts: 133
Location: India

PostPosted: Fri Mar 07, 2008 10:36 am    Post subject:
Reply with quote

"We have to compare two tables F1,F2 on pin_cd,pin_nm key"..

It seems like u are comparing only emp no?. Good enough if thats ur requirement..

I wud suggest the sub query to change to below as u need only an existence check..
(
SELECT 1 FROM B
WHERE B.EMP_NO = A.EMP_NO);

Thanks,
Prajesh
Back to top
View user's profile Send private message
vsgaikwad

New User


Joined: 20 Nov 2007
Posts: 6
Location: Pune

PostPosted: Fri Mar 07, 2008 11:47 am    Post subject:
Reply with quote

Hi Prajesh,

I tried above code mine & also given by you.
My query ran without error even your query also, but I seen that records are not deleted from table B.

Even I tried

SELECT * FROM A
WHERE NOT EXISTS (
SELECT * FROM B
WHERE B.EMP_NO = A.EMP_NO);

It's giving SQLCODE 100.

But when I tried


SELECT * FROM A INNER JOIN B ON
A.AC_NO != B.AC_NO;

It's giving many records. It's meaning is that records from A not get deleted. But I don't understand why it is not get deleted.


Thanks,
Vikas G.
Back to top
View user's profile Send private message
Prajesh_v_p

Active User


Joined: 24 May 2006
Posts: 133
Location: India

PostPosted: Fri Mar 07, 2008 11:55 am    Post subject:
Reply with quote

Its getting deleted.. But ur validation query is wrong..

SELECT * FROM A INNER JOIN B ON
A.AC_NO != B.AC_NO;

It would give many rows as each account number record can find at least 1 non matching record for account number. Hope u r getting this..
Back to top
View user's profile Send private message
vsgaikwad

New User


Joined: 20 Nov 2007
Posts: 6
Location: Pune

PostPosted: Fri Mar 07, 2008 12:19 pm    Post subject:
Reply with quote

Could u please explain little bit more, how this Query is invalid & what I am missing.



Vikas
Back to top
View user's profile Send private message
Prajesh_v_p

Active User


Joined: 24 May 2006
Posts: 133
Location: India

PostPosted: Fri Mar 07, 2008 12:26 pm    Post subject:
Reply with quote

Table A Table B
Act1 Act1
Act2 Act2

When the condition check for row1 comes,record from table A ie Act1, it can find at least one non matching record in table B ie Act2. hence it retrievs the row1..so on for row2. If you are still in confusion, you might have to go back to the manuals for join condition...
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 Identify top 30 big tables or indexes... ashek15 DB2 0 Fri Jun 16, 2017 10:01 am
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm
No new posts SPUFI -- Joining 3 tables – data in... Sysaron DB2 2 Wed Mar 08, 2017 4:18 am
No new posts To know activities on db2 tables ashek15 DB2 4 Fri Mar 03, 2017 11:23 pm
No new posts ERM-Diagram of SysIBM-Catalog-Tables Auryn DB2 2 Fri Feb 17, 2017 6:22 pm


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