How to get a distinct count of result set of multople table joins?

Hi everyone,

I haven't played with SQL for a while, but the following SQL returns ~700,000 records ( a lot of duplicates ), and I used a UNION with the same query to reduce the records down to ~250,000. Is there a more efficient solution, as the UNION takes some time to run? ~3 mins.

<QUERY>
  SELECT * FROM schema.tbl_A a, schema.tbl_B b, schema.tbl_C c,

                        schema.tbl_D d, schema.tbl_E e, schema.tbl_F
f,
                        schema.tbl_G g, schema.tbl_H h, schema.tbl_I
i,
                        schema.tbl_J j, schema.tbl_K k, schema.tbl_L
l,
                        schema.tbl_M m
              WHERE  a.accountid = b.ID(+)
                AND  a.custom1id = c.ID(+)
                AND  a.custom2id = d.ID(+)
                AND  a.custom3id = e.ID
                AND  a.custom4id = f.ID(+)
                AND  a.entityid = g.ID(+)
                AND  a.icpid = h.ID(+)
                AND  a.parentid = i.ID(+)
                AND  a.periodid = j.ID(+)
                AND  a.scenarioid = k.ID(+)
                AND  a.valueid = l.ID(+)
                AND  a.viewid = m.ID(+)
                AND  d.label IN ('XXX', 'XXX')
                AND  e.label IN ('XXX', 'XXX')
                AND  f.label = 'X'
                AND  k.label = 'XXX'
                AND  l.label = 'XXX'
                AND  h.label = 'X'
                AND  m.label IN ('X', 'X');

Problem - I am having some trouble getting a DISTINCT COUNT of the result set from the above. I trieed the
following, but I still get the same (non-distinct) amount returned for the count.

How do I get a count of the distinct records / result set? I tried with the UNION as above, as well as the follwoing, but still get a count which includes the duplicates.

SELECT COUNT(*) FROM
 (
  SELECT DISTINCT* FROM schema.tbl_A a, schema.tbl_B b, schema.tbl_C c,

                        schema.tbl_D d, schema.tbl_E e, schema.tbl_F
f,
                        schema.tbl_G g, schema.tbl_H h, schema.tbl_I
i,
                        schema.tbl_J j, schema.tbl_K k, schema.tbl_L
l,
                        schema.tbl_M m
              WHERE  a.accountid = b.ID(+)
                AND  a.custom1id = c.ID(+)
                AND  a.custom2id = d.ID(+)
                AND  a.custom3id = e.ID
                AND  a.custom4id = f.ID(+)
                AND  a.entityid = g.ID(+)
                AND  a.icpid = h.ID(+)
                AND  a.parentid = i.ID(+)
                AND  a.periodid = j.ID(+)
                AND  a.scenarioid = k.ID(+)
                AND  a.valueid = l.ID(+)
                AND  a.viewid = m.ID(+)
                AND  d.label IN ('XXX', 'XXX')
                AND  e.label IN ('XXX', 'XXX')
                AND  f.label = 'X'
                AND  k.label = 'XXX'
                AND  l.label = 'XXX'
                AND  h.label = 'X'
                AND  m.label IN ('X', 'X')

Thanks in advance,
Chris Received on Tue Feb 26 2008 - 22:26:10 CST