Details of different master records are equal - how to determine

[Shiva]

Hi,

Suppose I've got the following tables:

Tables 1

DeptCombNo
(PK)

1
2

Table 2

DeptCombNo Deptcode
(FK)

1 7NAH
1 4ZIC
2 3ZBE
3 4ZIC
3 7NAH

The field DeptCombNo of table 2 is foreign key to the primary key DeptCombNo
of table 1, iow table 2 contains the details of table 1.

Now the question is: how do I set up a SQL-query that can tell me that
details of DeptCombNo 1 and 3 are equal? I do not want to resort to PL-SQL
or any other 3rd generation programming language.

I'm using Oracle 8.1.7.

Thanks in advance!

[Mark C. Stock]

"Shiva" <shiv...@hotmail.com> wrote in message
news:cunp6j$gqj$1...@reader11.wxs.nl...

[Mark C. Stock]

"Mark C. Stock" <mcstockX@Xenquery .com> wrote in message
news:kIKdnRy9qq5...@comcast.com...

have you tried the MINUS operator?

++ mcs

[Thomas Kyte]

In article <cunp6j$gqj$1...@reader11.wxs.nl>, Shiva says...

ops$tkyte@ORA9IR2> select * from t;

ID STR
---------- -----

1 7NAH
1 4ZIC
2 3ZBE
3 4ZIC
3 7NAH

4 3ZBE

6 rows selected.

ops$tkyte@ORA9IR2> select *
2 from (
3 select id, data, count(*) over (partition by data) cnt
4 from (
5 select id,
6 rtrim(
7 max( decode(r,1,str) ) || '/' ||
8 max( decode(r,2,str) ) || '/' ||
9 max( decode(r,3,str) ) || '/' ||
10 max( decode(r,4,str) ) || '/' ||
11 max( decode(r,5,str) ) || '/' ||
12 max( decode(r,6,str) ) || '/' ||
13 max( decode(r,7,str) ) || '/' ||
14 max( decode(r,8,str) ) || '/' ||
15 max( decode(r,9,str) ), '/' ) data
16 from (select id, str, row_number() over (partition by id order by str) r
17 from t)
18 group by id
19 having max( decode( r, 10, 1/0, 0 ) ) = 0
20 )
21 )
22 where cnt > 1
23 order by data, id
24 /

ID DATA CNT
---------- ---------- ----------
2 3ZBE 2
4 3ZBE 2
1 4ZIC/7NAH 2
3 4ZIC/7NAH 2

4 rows selected.

(hint, run the queries from the inside out to see what they do piece by piece.
basically, we'll pivot the result set. I assumed 9 or less children, add more
max(decode's as needed. the max(decode( ... 1/0 ... ) ) will catch the issue
you would have if you have more than 9 (or whatever you deem the "correct max")
number of children...

--
Thomas Kyte
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corporation

[Shiva]

That does the trick.. although I haven't limited the number of details yet,
I can safely assume that the maximum is about 10 (usually much less).

Thanks a lot!

[Thomas Kyte]

In article <cuol56$1ri$1...@reader11.wxs.nl>, Shiva says...

I changed something as I was testing this -- the having should be:

19 having decode( sign(9-max(r)), -1, 1/0, 0 ) = 0

just to "be sure"

>> ID DATA CNT
>> ---------- ---------- ----------
>> 2 3ZBE 2
>> 4 3ZBE 2
>> 1 4ZIC/7NAH 2
>> 3 4ZIC/7NAH 2
>>
>> 4 rows selected.
>>
>>
>>
>> (hint, run the queries from the inside out to see what they do piece
>> by piece. basically, we'll pivot the result set. I assumed 9 or less
>> children, add more max(decode's as needed. the max(decode( ... 1/0
>> ... ) ) will catch the issue you would have if you have more than 9
>> (or whatever you deem the "correct max") number of children...
>
>