Archives of the TeradataForum
Message Posted: Wed, 05 Oct 2011 @ 19:25:53 GMT
<-- Anonymously Posted: Wednesday, October 05, 2011 14:17 -->
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.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|