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

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 /
>
> 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...
Received on Sun Feb 13 2005 - 16:32:44 CST