Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 13 Jan 2005 @ 16:59:01 GMT


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


Subj:   Re: Applying LEFT OUTER JOIN in the WHERE condition
 
From:   Dieter Noeth

Anomy Anom wrote:

  Is there a way I can apply the LEFT OUTER JOINS in the WHERE condition as we do in Oracle.  



You can probably move the condition on "T2"."COMPANY_KEY" to the ON part, but it depends on the expected result set.

     Select "T1"."ACCOUNT_KEY" "C0"
          , "T2"."COMPANY_KEY" "C1"
          , sum("GL_JOURNAL_DETAILS_F_V"."LOCAL_CURRENCY_AMOUNT") "C2"

          From ("EDW_REP_SOURCE"."GL_JOURNAL_DETAILS_F_V" "GL_JOURNAL_DETAILS_F_V"
                     LEFT OUTER JOIN "EDW_REP_SOURCE"."ACT_ACCOUNT_D_V" "T1"
             On "GL_JOURNAL_DETAILS_F_V"."AFF_ACCOUNT" = "T1"."ACCOUNT_KEY")
                     LEFT OUTER JOIN "EDW_REP_SOURCE"."ACT_COMPANY_D_V" "T2"
             On "GL_JOURNAL_DETAILS_F_V"."AFF_COMPANY" = "T2"."COMPANY_KEY"
                     AND "T2"."COMPANY_KEY" In ('500110', '510110')

     Where "GL_JOURNAL_DETAILS_F_V"."PERIOD_ID" In (200401,200402,200403)
       And "GL_JOURNAL_DETAILS_F_V"."AFF_COMPANY"  NOT  In ('310310')

     Group By "T1"."ACCOUNT_KEY", "T2"."COMPANY_KEY";

When you only used Oracle-style outer joins, you'd better read the manuals. There's a reprint from an old, but still excellent Teradata Review article:


SQL Reference: Data Manipulation Statements
Chapter 2: Join Expressions
Outer Join Case Study


Dieter



     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016