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
 

 

Compare a column in one table to the number of entries

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

New User


Joined: 07 Mar 2008
Posts: 1
Location: India

PostPosted: Thu Sep 04, 2008 10:17 am    Post subject: Compare a column in one table to the number of entries
Reply with quote

Hi,

I have two tables. I must compare a column in one table to the number of entries in the other under the same Identification number.

Eg:

Table 1 has a value of 5 under "col A" for a particular record.
Therefore table 2 must have 5 rows with the same ID number as the table 1 row.

Now using a join I have listed all the rows which have mismatches between Table 1 & Table 2.

Eg:

Table 1 has a value of 5 under "col A" for a particular record.
Table 2 has ONLY 4 rows with the same ID number as the table 1 row. Therefore this is a mismatch and the output file displays the ID number of the mismatched row.

My query is how do I generate a count for the number of mismatches?

Help would be appreciated :)
Back to top
View user's profile Send private message

ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Thu Sep 04, 2008 5:12 pm    Post subject:
Reply with quote

If i understud correctly table1 has id A and val as 4 so in table 2 there has to be 4 rows of A and you want this A to be displyed if no of rows in table2 is not 4 right ? if yes then


Code:


SELECT ID FROM TABLE1 A WHERE VAL <>
(SELECT COUNT(*) FROM TABLE2 WHERE ID = A.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 Storing huge volume of data, compare ... Pradeep K M All Other Mainframe Topics 3 Mon Jan 16, 2017 5:08 pm
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
No new posts SQZ pipe delimited file with 1 column... Rick Silvers DFSORT/ICETOOL 2 Sun Nov 06, 2016 8:11 pm
No new posts Match or compare two files in VB Format anatol DFSORT/ICETOOL 14 Thu Nov 03, 2016 7:41 pm


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