Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2009
    Posts
    7

    Unanswered: Outer join issue

    --------------------------------------------------------------------------------
    HERE IS MY CODE

    SELECT
    A,
    B,
    C,
    D,
    E

    FROM
    (
    SELECT
    A,
    B,
    C
    FROM VW1
    GROUP BY A) Query1,

    (SELECT
    A,
    D,
    E FROM VW1
    where week= (SELECT WEEK FROM CALNEDAR WHERE CALENDAR_DATE =( CURRENTDATE()-7))
    GROUP BY A) QUERY2

    WHERE
    QUERY1.A= QUERY2.A

    Now all rows are not being returned because the second query is briging back only records from last week.
    I need an outer join and I am not able to place an outer join in the where clause
    i am using terdata

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >i am using terdata
    We do Oracle here, so why are you posting here?

    It would be helpful if you provided DDL for tables involved.
    It would be helpful if you provided DML for test data.
    It would be helpful if you provided expected/desired results & a detailed explanation how
    & why the test data gets transformed or organized.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Dec 2009
    Posts
    7
    because there is no teradata here...and oracle code is the closest to teradata..

    Query1 & 2 gets aggregates and the query is written that way.

    A is like a HOTEL
    B is like profit
    C is like margin
    D is share
    E is expenses

    hotel Profit Margin Share Expense
    3 45 20 15 10
    4 35 15 10 8

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    go to a teradata forum.


    NCR: Teradata Forum - Tek-Tips
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by KETAN4U View Post
    I need an outer join and I am not able to place an outer join in the where clause
    Generally, an outer join is equivalent to a union of an inner join and a select that's to be on the outer side, kinda like this
    Code:
    select 
     whatever
    from
     query1, query2
    where
     QUERY1.A= QUERY2.A
    
    union all
    
    select
     whatever
    from 
     query1
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •