Results 1 to 2 of 2
  1. #1
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MS Query (joins) (2003 SP2)

    I'm trying to import data from 4 AS400 tables & getting the last table to 'relate' correctly is proving difficult.

    When I try to change the relationships, I get the message, "Can't have outer joins if there are more than two tables in the query".

    The 'parent' table is EIBLREP, with the problem 'child' table as EIBXREL0. I'd like to have all records from EIBLREP & matching ones from EIBXREL0, but this is when I get the message.

    SQL =
    SELECT
    EIBLREP.BLAEPP,
    EIBLREP.BLA0DT,
    EIBLREP.BLATNC,
    EIBLREP.BLJHST,
    EIBLREP.BLBDCD,
    EIBLREP.BLANCD,
    EIBLREP.BLA2NC,
    EIA4REP.A4B9NA,
    EIBGREP.BGGLPP,
    EIBLREP.BLC5ST,
    EIBLREP.BLACCD,
    EIBLREP.BLCDNA,
    EIBGREP.BGPVST,
    EIBLREP.BLCENA,
    EIBLREP.BLYYKD,
    EIBLREP.BLCRNC,
    EIBGREP.BGA0NC,
    EIBGREP.BGAONC,
    EIBGREP.BGAPNC,
    EIBGREP.BGBENC,
    EIBGREP.BGFRPN,
    EIBGREP.BGFUPN,
    EIBGREP.BGFSPN,
    EIBGREP.BGFTPN
    FROM
    ALCEDC01.EISGBRDTA.EIA4REP EIA4REP,
    ALCEDC01.EISGBRDTA.EIBGREP EIBGREP,
    ALCEDC01.EISGBRDTA.EIBLREP EIBLREP,
    ALCEDC01.EISGBRDTA.EIBXREL0 EIBXREL0
    WHERE
    EIBLREP.BLAEPP = EIBGREP.BGAEPP AND
    EIBLREP.BLATNC = EIBGREP.BGATNC AND
    EIBLREP.BLX0KD = EIBGREP.BGX0KD AND
    EIBLREP.BLA2NC = EIA4REP.A4A2NC AND
    EIBLREP.BLAEPP = EIBXREL0.BXAEPP AND
    EIBLREP.BLATNC = EIBXREL0.BXATNC AND
    EIBLREP.BLX0KD = EIBXREL0.BXX0KD AND
    ((EIBLREP.BLAEPP>='2003') AND (EIBLREP.BLA0DT Between ? And ?) AND (EIBLREP.BLC5ST In ('2','3','4')) AND (EIBLREP.BLMYKD='BAN'))

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: MS Query (joins) (2003 SP2)

    Can you change

    EIBLREP.BLAEPP = EIBXREL0.BXAEPP

    to

    (EIBLREP.BLAEPP = EIBXREL0.BXAEPP OR EIBXREL0.BXAEPP Is Null)

    and similar for the others, or is that when you get the error message?

Posting Permissions

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