Sub-select in the ON clause of a LEFT JOIN

tyrrell · #1 (**permalink**) 08-26-04, 10:09

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

famudba · #2 (**permalink**) 08-26-04, 11:37

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

Damian Ibbotson · #3 (**permalink**) 08-26-04, 11:45

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

Damian Ibbotson · #4 (**permalink**) 08-27-04, 03:49

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

Damian Ibbotson · #5 (**permalink**) 08-27-04, 04:15

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

Internet Services

Web Development

Digital Marketing

Consumer Tech