By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,611 Members | 1,693 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,611 IT Pros & Developers. It's quick & easy.

Self Join in DB2 SQL

P: 2

In a trasaction table there are some erroneous records in a column called Policynumber that need to be indentified. There is a column called Tran type which can have values "A" and "D". For every record with tran type 'A' there will be a corresponding record with tran_typ "D". So we wanted to check whether the errorneous policy records have matching "A" records and "D" records.

We used the following Self Join.

"Select count(*) from Tran_table A, Tran_table B
where A.tran_typ = 'A' and A.Polnum like '%,%'
and B.tran_typ = 'D' and B.Polnum like '%,%'
and A.POlnum = B.polnum

It gave a result as 10482

But we used another query (correlated sub query)

Select count(*) from Tran_table B
b.polnum in (
select polnum from tran_table A
where A.tran_typ = 'A' and A.Polnum like '%,%'
b.Tran_typ = 'D'

This query gave a result as "9048"

When we analysed the table , the second result is correct. as we had 9048 "A" records, 9071 D records. And we found that 23 "D" records does not have matching "A" records.

We are not sure what was the error in the first query. Why did we get a wierd result. ?
Jun 22 '07 #1
Share this Question
Share on Google+
1 Reply

P: 24
As far as i can tell the problem seems to be that you have multiple A and D records will the same polnum value. So I do not mean just one A record and one D record for a single polnum value but, for examples, 2 A records and 1 D record with the same polnum value.

On the second query this would just show as 1 on the count, but on your first SQL this would show as 2 as the SQL in the first statements joins all A records to all D records were the polnum is the same so in the example would return 2 records.

The way to check this is to execute the following SQL:

Select tran_typ, Polnum, count(*) from Tran_table
where (tran_typ = 'A' or tran_typ = 'D')
and Polnum like '%,%'
group by tran_typ, Polnum
having count(*) > 1

This will then show you all the occurrences of multiple A or D records for the same Polnum value.

Obviously, this may not be an incorrect situation as this may be what you intend for your data but it would explain the results you got from the two SQL statements.


Jun 30 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.