Self Join in DB2 SQL

[Pragathiarun]

Hi,

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
where
b.polnum in (
select polnum from tran_table A
where A.tran_typ = 'A' and A.Polnum like '%,%'
)
and
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. ?

[Snib]

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.

Regards

Snib