Archives of the TeradataForum

Message Posted: Mon, 21 Apr 2009 @ 00:37:35 GMT

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

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?

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
     ;

Thank you all in advance for your assistance.

Shaun

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