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

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 13 Feb 2005 08:22:38 -0800
Message-ID: <118311758.000061bb.013@drn.newsguy.com>

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

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

Received on Sun Feb 13 2005 - 10:22:38 CST