Archives of the TeradataForum

Message Posted: Wed, 05 Oct 2011 @ 20:30:51 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Anomy.Anom wrote:

Any subquery can be rewritten as a join, probably:

     (
       SELECT
       SUM(t1_inner.amount)
       FROM TAB1 t1_inner INNER JOIN TAB5 t5
       ON t1_inner.f = t5.f
       join TAB1 sub1
       on t1_inner.f = sub1.f
       WHERE
           t1_inner.a = t3_outer.a
       AND sub1.a = t1_outer.a
       AND t5.flag = 'Y'
     )

If sub1.f is not unique you'll need a DISTINCT somewhere.

But this query returns detail rows plus an aggregate, which is usually done using OLAP functions:

SUM(t1_inner.amount) OVER (PARTITION BY f) (or a variation) might return the same result.

You might even be able to replace the whole scalar subquery with a single join to TAB5 and an OLAP function (in a derived table).

Dieter

	<Prev	Next>		<<First	<Prev	Next>	Last>>