ORA-00918: column ambiguously defined
I have not been able to find out what column is ambiguously defined.
However, I think that the problem relates to the second outer join.
I am planning on using this sql to do a bulk load into a PL/SQL collection.
Any help would be appreciated.
Thanks.
Code:
1 SELECT sles.sec_id, sles.secw_seq_id,
2 sles.stlm_catg_typ_cd, sles.clps_rat_meth_c,
3 sles.sles_est_y_m_dt,
4 sles.sles_est_req_incm_a,
5 CASE
6 WHEN sles.clps_rat_meth_c IN (4,20,22) THEN
7 sles.sles_prr_adjm_at
8 WHEN sles.clps_rat_meth_c IN (19,25,37,38) THEN
9 GREATEST( sles.sles_prr_adjm_at, 0 )
10 WHEN sles.clps_rat_meth_c IN (2,5,6,7,23,24,26) THEN
11 0
12 ELSE
13 sles.sles_prr_adjm_at
14 END sles_prr_adjm_at,
15 ( sles.sles_sec_ernd_at - NVL(sles_prior.sles_prr_adjm_at, 0))
16 sles_adjd_ernd_at,
17 (( sesl.sles_revd_adjm_at * sles.sles_srp_at ) / sesl.sesl_srp_at )
18 sles_revd_adjm_at,
19 ( sesl.sles_adjd_ernd_at + sesl.sles_revd_adjm_at )
20 sles_bkd_at
21 BULK COLLECT INTO r_sles
22 FROM ( SELECT se_id, sec_id,
23 sew_seq_id, secw_seq_id,
24 clps_rat_meth_c, sles_est_y_m_dt,
25 stlm_catg_typ_cd, sles_sec_ernd_at,
26 sles_srp_at, sles_est_req_incm_a
27 FROM sles
28 WHERE sles_est_y_m_dt = '200407') sles
29 LEFT OUTER JOIN
30 ( SELECT se_id sesl_se_id,
31 sew_seq_id sesl_sew_seq_id,
32 clps_rat_meth_c sesl_clps_rat_meth_c,
33 stlm_catg_typ_cd sesl_stlm_catg_typ_cd,
34 sesl_est_y_m_dt, sesl_revd_adjm_at,
35 sesl_srp_at, sesl_adjd_ernd_at,
36 sesl_revd_adjm_at
37 FROM sesl
38 WHERE sesl_est_y_m_dt = '200407') sesl
39 ON sles.se_id = sesl.sesl_se_id AND
40 sles.sew_seq_id = sesl.sesl_sew_seq_id AND
41 sles.clps_rat_meth_c = sesl.sesl_clps_rat_meth_c AND
42 sles.stlm_catg_typ_cd = sesl.sesl_stlm_catg_typ_cd
43 LEFT OUTER JOIN
44 ( SELECT sec_id, secw_seq_id,
45 clps_rat_meth_c, sles_est_y_m_dt,
46 sles_revd_adjm_at, sles_prr_adjm_at
47 FROM sles
48 WHERE sles_est_y_m_dt = '200407' ) sles_prior
49 ON sles.sec_id = sles_prior.sec_id AND
50 sles.secw_seq_id = sles_prior.secw_seq_id AND
51* sles.clps_rat_meth_c = sles_prior.clps_rat_meth_c
LIDXPNN - t31a=> /
SELECT sles.sec_id, sles.secw_seq_id,
*
ERROR at line 1:
ORA-00918: column ambiguously defined
Elapsed: 00:00:00.94
LIDXPNN - t31a=> spool off
