Re: Details of different master records are equal - how to determine

In article <cuol56$1ri$1_at_reader11.wxs.nl>, Shiva says...
>
>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 wrote:
>> In article <cunp6j$gqj$1_at_reader11.wxs.nl>, Shiva says...
>>>
>>> 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!
>>>
>>>
>>
>> ops$tkyte_at_ORA9IR2> select * from t;
>>
>> ID STR
>> ---------- -----
>> 1 7NAH
>> 1 4ZIC
>> 2 3ZBE
>> 3 4ZIC
>> 3 7NAH
>> 4 3ZBE
>>
>> 6 rows selected.
>>
>> ops$tkyte_at_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 /
>>

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...
>
>

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