Looking for left outer join help on this sql. - IBM AS400

This is a discussion on Looking for left outer join help on this sql. - IBM AS400 ; SELECT ALL D.BPASDT, D.BPASTS, A.ABABTX, A.ABACCD, A.ABAECD, SUBSTR(C.ACAKCD,1,4)||'-'||SUBSTR(C.ACALCD,1,2)||'-'|| SUBSTR( C.ACAMCD,1,4) AS PARCEL, A.ABAUCD, A.ABAJCD, B.ANBKCD, B.ANALTX, D.BPATYP, E.BPATDS, D.BPPYER, D.BPPCNB FROM HTEDTA/LMABREP A, HTEDTA/LMANREP B, HTEDTA/LMACREP C, HTEDTA/BP200AP D, HTEDTA/BP960AP E WHERE A.ABBKCD = B.ANBKCD AND A.ABAJCD = C.ACAJCD AND ...

+ Reply to Thread
Results 1 to 3 of 3

Thread: Looking for left outer join help on this sql.

  1. Looking for left outer join help on this sql.

    SELECT
    ALL D.BPASDT, D.BPASTS, A.ABABTX, A.ABACCD,
    A.ABAECD,
    SUBSTR(C.ACAKCD,1,4)||'-'||SUBSTR(C.ACALCD,1,2)||'-'||
    SUBSTR(
    C.ACAMCD,1,4) AS PARCEL, A.ABAUCD, A.ABAJCD, B.ANBKCD,
    B.ANALTX,
    D.BPATYP, E.BPATDS, D.BPPYER,
    D.BPPCNB
    FROM HTEDTA/LMABREP
    A,
    HTEDTA/LMANREP
    B,
    HTEDTA/LMACREP
    C,
    HTEDTA/BP200AP
    D,
    HTEDTA/BP960AP
    E
    WHERE A.ABBKCD =
    B.ANBKCD
    AND A.ABAJCD =
    C.ACAJCD
    AND A.ABAUCD =
    D.BPLCID
    AND D.BPATYP =
    E.BPATCD
    AND( D.BPASTS =
    'CO'
    AND D.BPPYER = 7
    AND D.BPASDT BETWEEN 070801 AND 070831)
    ORDER BY E.BPATDS ASC, D.BPASDT ASC, A.ABABTX ASC


  2. Re: Looking for left outer join help on this sql.

    On Aug 16, 10:00 am, jacko wrote:
    > SELECT
    > ALL D.BPASDT, D.BPASTS, A.ABABTX, A.ABACCD,
    > A.ABAECD,
    > SUBSTR(C.ACAKCD,1,4)||'-'||SUBSTR(C.ACALCD,1,2)||'-'||
    > SUBSTR(
    > C.ACAMCD,1,4) AS PARCEL, A.ABAUCD, A.ABAJCD, B.ANBKCD,
    > B.ANALTX,
    > D.BPATYP, E.BPATDS, D.BPPYER,
    > D.BPPCNB
    > FROM HTEDTA/LMABREP
    > A,
    > HTEDTA/LMANREP
    > B,
    > HTEDTA/LMACREP
    > C,
    > HTEDTA/BP200AP
    > D,
    > HTEDTA/BP960AP
    > E
    > WHERE A.ABBKCD =
    > B.ANBKCD
    > AND A.ABAJCD =
    > C.ACAJCD
    > AND A.ABAUCD =
    > D.BPLCID
    > AND D.BPATYP =
    > E.BPATCD
    > AND( D.BPASTS =
    > 'CO'
    > AND D.BPPYER = 7
    > AND D.BPASDT BETWEEN 070801 AND 070831)
    > ORDER BY E.BPATDS ASC, D.BPASDT ASC, A.ABABTX ASC


    You don't give us much info, but if you just want to change the inner
    join to a left outer join on all tables, try this

    SELECT
    D.BPASDT, D.BPASTS, A.ABABTX, A.ABACCD,
    A.ABAECD, SUBSTR(C.ACAKCD,1,4)||'-'||SUBSTR(C.ACALCD,1,2)||'-'||
    SUBSTR(C.ACAMCD,1,4) AS PARCEL, A.ABAUCD, A.ABAJCD, B.ANBKCD,
    B.ANALTX, D.BPATYP, E.BPATDS, D.BPPYER,
    D.BPPCNB

    FROM HTEDTA/LMABREP A

    Left Outer Join HTEDTA/LMANREP B
    on A.ABBKCD = B.ANBKCD

    Left Outer Join HTEDTA/LMACREP C
    on A.ABAJCD = C.ACAJCD

    Left outer Join HTEDTA/BP200AP D
    on A.ABAUCD = D.BPLCID

    Left outer Join HTEDTA/BP960AP E
    on D.BPATYP = E.BPATCD

    WHERE
    D.BPASTS = 'CO'
    AND D.BPPYER = 7
    AND D.BPASDT BETWEEN 070801 AND 070831

    ORDER BY E.BPATDS ASC, D.BPASDT ASC, A.ABABTX ASC


  3. Re: Looking for left outer join help on this sql.


    "Graybeard" skrev i en meddelelse
    news:1187273396.347083.284180@r29g2000hsg.googlegr oups.com...
    > On Aug 16, 10:00 am, jacko wrote:
    >> SELECT
    >> ALL D.BPASDT, D.BPASTS, A.ABABTX, A.ABACCD,
    >> A.ABAECD,
    >> SUBSTR(C.ACAKCD,1,4)||'-'||SUBSTR(C.ACALCD,1,2)||'-'||
    >> SUBSTR(
    >> C.ACAMCD,1,4) AS PARCEL, A.ABAUCD, A.ABAJCD, B.ANBKCD,
    >> B.ANALTX,
    >> D.BPATYP, E.BPATDS, D.BPPYER,
    >> D.BPPCNB
    >> FROM HTEDTA/LMABREP
    >> A,
    >> HTEDTA/LMANREP
    >> B,
    >> HTEDTA/LMACREP
    >> C,
    >> HTEDTA/BP200AP
    >> D,
    >> HTEDTA/BP960AP
    >> E
    >> WHERE A.ABBKCD =
    >> B.ANBKCD
    >> AND A.ABAJCD =
    >> C.ACAJCD
    >> AND A.ABAUCD =
    >> D.BPLCID
    >> AND D.BPATYP =
    >> E.BPATCD
    >> AND( D.BPASTS =
    >> 'CO'
    >> AND D.BPPYER = 7
    >> AND D.BPASDT BETWEEN 070801 AND 070831)
    >> ORDER BY E.BPATDS ASC, D.BPASDT ASC, A.ABABTX ASC

    >
    > You don't give us much info, but if you just want to change the inner
    > join to a left outer join on all tables, try this
    >
    > SELECT
    > D.BPASDT, D.BPASTS, A.ABABTX, A.ABACCD,
    > A.ABAECD, SUBSTR(C.ACAKCD,1,4)||'-'||SUBSTR(C.ACALCD,1,2)||'-'||
    > SUBSTR(C.ACAMCD,1,4) AS PARCEL, A.ABAUCD, A.ABAJCD, B.ANBKCD,
    > B.ANALTX, D.BPATYP, E.BPATDS, D.BPPYER,
    > D.BPPCNB
    >
    > FROM HTEDTA/LMABREP A
    >
    > Left Outer Join HTEDTA/LMANREP B
    > on A.ABBKCD = B.ANBKCD
    >
    > Left Outer Join HTEDTA/LMACREP C
    > on A.ABAJCD = C.ACAJCD
    >
    > Left outer Join HTEDTA/BP200AP D
    > on A.ABAUCD = D.BPLCID
    >
    > Left outer Join HTEDTA/BP960AP E
    > on D.BPATYP = E.BPATCD
    >
    > WHERE
    > D.BPASTS = 'CO'
    > AND D.BPPYER = 7
    > AND D.BPASDT BETWEEN 070801 AND 070831
    >
    > ORDER BY E.BPATDS ASC, D.BPASDT ASC, A.ABABTX ASC
    >


    Hmmm... Not much point in specifying outer joins unless you also allow null
    values in your WHERE clause:


    WHERE (D.BPASTS IS NULL OR D.BPASTS = 'CO' )
    AND (D.BPPYER IS NULL OR D.BPPYER = 7 )
    AND (D.BPASDT IS NULL OR D.BPASDT BETWEEN 070801 AND 070831)





+ Reply to Thread