Archives of the TeradataForum

Message Posted: Wed, 05 Oct 2011 @ 19:25:53 GMT

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

<-- Anonymously Posted: Wednesday, October 05, 2011 14:17 -->

Hi Folks,

I am trying to convert a sql written in Oracle to Teradata. The oracle version of the query has scalar subquery which in turn has a subquery that references one of the tables in the outer/main query. We currently use Teradata v12 which does not scalar subqueries. I can convert the scalar subquery into a left outer join in Teradata. However, the problem still remains when connecting the inner subquery to the main query, since this will not be allowed inside a derived table ( in case of the left outer join).

Please see an example of the Oracle-version below :

     SELECT t1_outer.a, t1_outer.b, t2_outer.j,
     (
      SELECT
      SUM(t1_inner.amount)
      FROM TAB1 t1_inner INNER JOIN TAB5 t5
      ON t1_inner.f = t5.f
      WHERE
          t1_inner.a = t3_outer.a
      AND t1_inner.f IN ( SELECT f FROM TAB1 sub1 WHERE sub1.a = t1_outer.a )
      AND t5.flag = 'Y'
     ) sum_of_amt
     FROM
     TAB1 t1_outer
     INNER JOIN TAB2 t2_outer ON t1_outer.a = t2_outer.a
     INNER JOIN TAB1 t3_outer ON t1_outer.a = t3_outer.b
     LEFT OUTER JOIN TAB4 t4_outer ON t2_outer.c = t4_outer.c

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.

Could anyone please advise.

Thanks much.

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