Home Page for the TeradataForum

Archives of the TeradataForum

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

  <Prev Next>  
Next> Last>>  

Subj:   Subqueries inside Scalar subqueries
From:   Anomy Anom

<-- 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,
      FROM TAB1 t1_inner INNER JOIN TAB5 t5
      ON t1_inner.f = t5.f
          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
     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>  
Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023