Archives of the TeradataForum

Message Posted: Tue, 21 Apr 2009 @ 09:30:49 GMT


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

Subj:		Re: SELECT Subquery

From:		Dieter Noeth

Shaun Steckley wrote:

I started writing a query today where I thought I would just use a subquery and finish it quickly. However, after researching a good part of today it seems like Teradata does not allow subqueries in the SELECT division. Is this correct?

Scalar Subqueries were missing for years, but finally it will be implemented in TD13.

If so, could someone please help me determine the best way to re-write the following query? I need to pull the most recent transaction of a specific type from the transaction table and return it with each for each loan record in the Loan table. I am trying to do it in a single query.

          > SELECT
          >                 Loan.LoanID,
          >                 (
          >                                 SELECT TOP 1 Amount from Tran WHERE
          > Tran.LoanID = Loan.LoanID AND Tran.Type = 'D' ORDER BY PostDate DESC
          >                 ) Tran_Amount
          > FROM
          >                 Loan
          > ;

A Scalar Subquery is usually rewritten as an Outer Join:

     SELECT
        Loan.LoanID,
        dt.Tran_amount
     FROM
        Loan left join
        (select LoanId, Amount as Tran_amount
         from Tran
         where Tran.Type = 'D'
         qualify
           row_number() over (partition by LoanId ORDER BY PostDate DESC) = 1
        ) dt
     on Loan.LoanID = dt.LoanID

Dieter


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

Attachments

Library

Quick Reference