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>>

Attachments

Library

Quick Reference