Home Page for the TeradataForum

Archives of the TeradataForum

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

  <Prev Next>   <<First <Prev

Subj:   Re: Subqueries inside Scalar subqueries
From:   Dieter Noeth

Anomy.Anom wrote:

  In the above example, i could convert the scalar subquery (sum_of_amt) into a derived table joined to the main query by LEFT OUTER JOIN, but I am not being able to put the subquery inside the derived table.  

Any subquery can be rewritten as a join, probably:

       FROM TAB1 t1_inner INNER JOIN TAB5 t5
       ON t1_inner.f = t5.f
       join TAB1 sub1
       on t1_inner.f = sub1.f
           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).


  <Prev Next>   <<First <Prev
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023