Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2004
    Posts
    3

    Question Unanswered: Sub-select in the ON clause of a LEFT JOIN

    After adding the last line (in RED) to the following query, DB2 returns the error "[IBM][CLI Driver][DB2/6000] SQL0338N An ON clause associated with a JOIN operator is not valid. SQLSTATE=42972"

    I interpret this to mean that I can't do a sub-select in an outer join, but that seems unlikely for a big iron DBMS . Would somebody please help me with this? Thanks!

    select
    count(*)
    from
    CNSMR_LN_TH ln

    INNER JOIN ACCOUNT acct
    on (ln.period_dt = CURRENT DATE - (DAY(CURRENT DATE) - 1 ) DAYS - 1 MONTH
    and ln.lob_cd = 'USC'
    and ln.cust_acct_id=acct.cust_acct_id)

    INNER JOIN CUST_X_ACCT acctx
    ON (acctx.CUST_ACCT_ID=acct.CUST_ACCT_ID
    AND acctx.CUST_ACCT_RLTNP_CD='P')

    INNER JOIN CUSTOMER cust
    ON (cust.CUSTOMER_ID=acctx.CUSTOMER_ID)

    LEFT JOIN APPLICATION app
    on (app.application_id = acct.application_id )

    LEFT JOIN ADDRESS_TH addr
    ON (addr.cust_acct_id = acct.cust_acct_id
    and addr.period_dt=CURRENT DATE - (DAY(CURRENT DATE) - 1 ) DAYS - 1 MONTH)

    LEFT JOIN NB_ACCT_TRAN nb
    on (nb.opr_acct_nr=acct.opr_acct_nr
    and nb.NB_TRAN_POST_DT between CURRENT DATE - (DAY(CURRENT DATE) - 1 ) DAYS - 1 MONTH and CURRENT DATE - (DAY(CURRENT DATE)) DAYS
    and nb.NB_TRAN_TYP_CD in ('A2', 'B2', 'AU', 'BU', 'MR', 'NR', 'AV', 'BV')
    and nb.nb_tran_seq_nr = (select MAX(nb2.nb_tran_seq_nr) from NB_ACCT_TRAN nb2 where nb2.opr_acct_nr=nb.opr_acct_nr))

  2. #2
    Join Date
    Jan 2004
    Location
    Tallahassee, FL, USA
    Posts
    96
    join or inner join mean joining columns in the table

    rest of the condition specify at where clouse other wise get error as you specified.

    you need put your SQL as below



    select count(*)
    from CNSMR_LN_TH ln
    INNER JOIN ACCOUNT acct
    on ( ln.cust_acct_id=acct.cust_acct_id)
    INNER JOIN CUST_X_ACCT acctx
    ON ( acctx.CUST_ACCT_ID=acct.CUST_ACCT_ID
    AND acctx.CUST_ACCT_RLTNP_CD='P')
    INNER JOIN CUSTOMER cust
    ON (cust.CUSTOMER_ID=acctx.CUSTOMER_ID)
    LEFT JOIN APPLICATION app
    on (app.application_id = acct.application_id )
    LEFT JOIN ADDRESS_TH addr
    ON (addr.cust_acct_id = acct.cust_acct_id )
    LEFT JOIN NB_ACCT_TRAN nb
    on (nb.opr_acct_nr=acct.opr_acct_nr)
    where ln.lob_cd = 'USC'
    and ln.period_dt = CURRENT DATE - (DAY(CURRENT DATE) - 1 ) DAYS - 1 MONTH
    and addr.period_dt = CURRENT DATE - (DAY(CURRENT DATE) - 1 ) DAYS - 1 MONTH)
    and nb.NB_TRAN_POST_DT between CURRENT DATE - (DAY(CURRENT DATE) - 1 ) DAYS - 1 MONTH and CURRENT DATE - (DAY(CURRENT DATE)) DAYS
    and nb.NB_TRAN_TYP_CD in ('A2', 'B2', 'AU', 'BU', 'MR', 'NR', 'AV', 'BV')
    and nb.nb_tran_seq_nr = ( select MAX(nb2.nb_tran_seq_nr)
    from NB_ACCT_TRAN nb2
    where nb2.opr_acct_nr=nb.opr_acct_nr);



    let me know , if any questions.


    Thank You
    Lekharaju Ennam
    Certified Oracle8i & DB UDB DBA
    Florida A&M University

  3. #3
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    The solution above is not doing the same as the OP was attempting. Outer joins NEVER exclude any rows, they just supply nulls where the join criteria are not met. The WHERE predicate is going to filter rows where previously they would not have been!

    Also, I've had problems with slow running queries when applying join criteria in a where clause after LEFT joins. The optimiser (quite rightly in many instances) cannot apply the filter until after the join has been resolved.

    I would use a NTE as below:

    Code:
    left join ( select n.*
                from ( select opr_acct_nr, max(nb_tran_seq_nr) nb_tran_seq_nr
                       from nb_acct_tran
                       group by opr_acct_nr
                     ) max_seq_nr
                ,    nb_acct_tran n
                where n.opr_acct_nr = max_seq_nr.opr_acct_nr
                  and n.nb_tran_seq_nr = max_seq_nr.nb_tran_seq_nr
              ) nb
      on  nb.opr_acct_nr = a.opr_acct_nr
      and nb.nb_tran_post_dt between current date - (day(current date) - 1 ) days - 1 month
      and current date - (day(current date)) days
      and nb.nb_tran_typ_cd in ('A2', 'B2', 'AU', 'BU', 'MR', 'NR', 'AV', 'BV')
    PS check the SQL carefully 'cos I've probably made a mistake ;-)

  4. #4
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    I wasn't entirely sure if you wanted the maximum sequence number regardless and then to supply nulls where the date is not within the range and the transaction type is incorrect (and the sequence number is not the same). In which case, you could tune it like this...

    Code:
    left join ( select n.*
                from ( select opr_acct_nr, max(nb_tran_seq_nr) nb_tran_seq_nr
                       from nb_acct_tran
                       group by opr_acct_nr
                     ) max_seq_nr
                ,    nb_acct_tran n
                where n.opr_acct_nr = max_seq_nr.opr_acct_nr
                  and n.nb_tran_seq_nr = max_seq_nr.nb_tran_seq_nr
                      and n.nb_tran_post_dt between current date - (day(current date) - 1 ) days - 1 month
                  and current date - (day(current date)) days
                  and nb.nb_tran_typ_cd in ('A2', 'B2', 'AU', 'BU', 'MR', 'NR', 'AV', 'BV')
              ) nb
      on  nb.opr_acct_nr = acct.opr_acct_nr
    ...but if you're only interested in preserving rows for the sequnce numbers where the date and transaction type are correct, use:

    Code:
    left join ( select n.*
                from ( select opr_acct_nr, max(nb_tran_seq_nr) nb_tran_seq_nr
                       from nb_acct_tran
                       where nb_tran_post_dt between current date - (day(current date) - 1 ) days - 1 month
                             and current date - (day(current date)) days
                         and nb_tran_typ_cd in ('A2', 'B2', 'AU', 'BU', 'MR', 'NR', 'AV', 'BV')
                       group by opr_acct_nr
                     ) max_seq_nr
                ,    nb_acct_tran n
                where n.opr_acct_nr = max_seq_nr.opr_acct_nr
                  and n.nb_tran_seq_nr = max_seq_nr.nb_tran_seq_nr
              ) nb
      on  nb.opr_acct_nr = acct.opr_acct_nr
    In the first example, the initial sub-select in the NTE to attain the required sequence number might impact performance if your table is large. I very much doubt that the optimiser would be able to effectively rewrite the query for you (but you never know). And I don't think there is anyway to correlate an NTE with the outer query, which would really help matters!

    Damian
    Last edited by Damian Ibbotson; 08-27-04 at 04:58.

  5. #5
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    ... and depending on how your nb_acct_tran is structured, you might want to duplicate the date and transaction type clause throughout. Nasty... but possibly necessary!

Posting Permissions

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