SELECT ac.ac_id,
clnt.clnt_class_cde,
en.enty_typ, en.id_typ,
nvl(ac.ovrided_ac_nam, en.mis_cust_nam) name,
addr.has_prc_id,
addr.has_prc_addr,
nvl(sus_ac.total,0) suspend_total,
mc.AC_ID,
mc.DAY_OF_MRGN_CALL,
mc.DAY_OF_MRGN_CALL2,
mc.INSUFFICIENT_AMT,
mc.MRGN_REQ,
mc.MRGN_CALL_AMT,
flmt.offl_expur_lmt,
nvl(repay.RPYMT_ID, 0),
mr.MRKT_GRP_ID,
mr.MC_AC_MRGN_PSTR,
mr.mamount
FROM cc_ac ac
inner join MC_AC_MRGN_CALL_DSP mc
on ac.ac_id = mc.AC_ID
inner join cc_clnt clnt
on ac.primy_clnt_id = clnt.clnt_id AND clnt.entrp_cde = ac.entrp_cde
inner join cc_enty en
on en.enty_seq = clnt.enty_seq
inner join cc_clnt_fincl_lmt flmt
on flmt.clnt_id = clnt.clnt_id
inner JOIN (SELECT primy_clnt_id, COUNT(*) TOTAL
FROM cc_ac
WHERE ac_stat='SUSPENDED' GROUP BY primy_clnt_id) sus_ac
ON clnt.clnt_id = primy_clnt_id
inner JOIN cc_clnt_addr addr ON clnt.clnt_id = addr.clnt_id
AND clnt.entrp_cde = addr.entrp_cde
inner JOIN MC_RPYMT repay
ON repay.AC_ID = ac.ac_id
inner JOIN (SELECT mgroupMem.MRKT_GRP_ID,
rmAcct.PSTR_SEQ,
rmAcct.PSTR,
margin.AC_ID,
(margin.INIT_MRGN * exr.MID_RATE) mamount
FROM MC_AC_MRGN_PSTR rmAcct,
MC_AC_MRGN margin,
MC_MRKT_GRP mgroupMem,
MC_EXCH_RATE exr
WHERE rmAcct.AC_MRGN_ID = margin.AC_MRGN_ID
AND margin.MRKT_ID = mgroupMem.MRKT_GRP_ID
AND (mgroupMem.MRKT_GRP_ID = 1
OR mgroupMem.MRKT_GRP_ID = 2)
AND margin.CCY_CDE = exr.CCY_CDE
AND exr.AGAINST_CCY_CDE = 'aaa'
ORDER BY mgroupMem.MRKT_GRP_ID, rmAcct.PSTR_SEQ) mr
ON ac.ac_id = mr.AC_ID
WHERE
mc.businessDate = '13-4月 -08 12.00.00.000000 上午'
AND repay.RPYMT_EFF_DATE <= '13-4月 -08 12.00.00.000000 上午'
AND repay.RPYMT_EXPR_DATE > '13-4月 -08 12.00.00.000000 上午'
AND ac.entrp_cde = 'TF'
AND en.entrp_cde = ac.entrp_cde
ORDER BY mc.AC_ID, mr.PSTR_SEQ, clnt.clnt_class_cde, en.enty_typ, en.id_typ, suspend_total
这条SQL语句出现‘ora-00918 未明确定义列’错误,请高手帮忙,急用。谢谢!
clnt.clnt_class_cde,
en.enty_typ, en.id_typ,
nvl(ac.ovrided_ac_nam, en.mis_cust_nam) name,
addr.has_prc_id,
addr.has_prc_addr,
nvl(sus_ac.total,0) suspend_total,
mc.AC_ID,
mc.DAY_OF_MRGN_CALL,
mc.DAY_OF_MRGN_CALL2,
mc.INSUFFICIENT_AMT,
mc.MRGN_REQ,
mc.MRGN_CALL_AMT,
flmt.offl_expur_lmt,
nvl(repay.RPYMT_ID, 0),
mr.MRKT_GRP_ID,
mr.MC_AC_MRGN_PSTR,
mr.mamount
FROM cc_ac ac
inner join MC_AC_MRGN_CALL_DSP mc
on ac.ac_id = mc.AC_ID
inner join cc_clnt clnt
on ac.primy_clnt_id = clnt.clnt_id AND clnt.entrp_cde = ac.entrp_cde
inner join cc_enty en
on en.enty_seq = clnt.enty_seq
inner join cc_clnt_fincl_lmt flmt
on flmt.clnt_id = clnt.clnt_id
inner JOIN (SELECT primy_clnt_id, COUNT(*) TOTAL
FROM cc_ac
WHERE ac_stat='SUSPENDED' GROUP BY primy_clnt_id) sus_ac
ON clnt.clnt_id = primy_clnt_id
inner JOIN cc_clnt_addr addr ON clnt.clnt_id = addr.clnt_id
AND clnt.entrp_cde = addr.entrp_cde
inner JOIN MC_RPYMT repay
ON repay.AC_ID = ac.ac_id
inner JOIN (SELECT mgroupMem.MRKT_GRP_ID,
rmAcct.PSTR_SEQ,
rmAcct.PSTR,
margin.AC_ID,
(margin.INIT_MRGN * exr.MID_RATE) mamount
FROM MC_AC_MRGN_PSTR rmAcct,
MC_AC_MRGN margin,
MC_MRKT_GRP mgroupMem,
MC_EXCH_RATE exr
WHERE rmAcct.AC_MRGN_ID = margin.AC_MRGN_ID
AND margin.MRKT_ID = mgroupMem.MRKT_GRP_ID
AND (mgroupMem.MRKT_GRP_ID = 1
OR mgroupMem.MRKT_GRP_ID = 2)
AND margin.CCY_CDE = exr.CCY_CDE
AND exr.AGAINST_CCY_CDE = 'aaa'
ORDER BY mgroupMem.MRKT_GRP_ID, rmAcct.PSTR_SEQ) mr
ON ac.ac_id = mr.AC_ID
WHERE
mc.businessDate = '13-4月 -08 12.00.00.000000 上午'
AND repay.RPYMT_EFF_DATE <= '13-4月 -08 12.00.00.000000 上午'
AND repay.RPYMT_EXPR_DATE > '13-4月 -08 12.00.00.000000 上午'
AND ac.entrp_cde = 'TF'
AND en.entrp_cde = ac.entrp_cde
ORDER BY mc.AC_ID, mr.PSTR_SEQ, clnt.clnt_class_cde, en.enty_typ, en.id_typ, suspend_total
这条SQL语句出现‘ora-00918 未明确定义列’错误,请高手帮忙,急用。谢谢!
解决方案 »
- 求一函数,有关字符串替换
- oracle升级到10.2.0.4后,启动数据库提示错误ORA-01034:ORACLE not available
- non-embedded database中文怎么译
- 我在RedhatLinux 9.2上装oracle 10g 遇到的问题,请各位高手帮帮忙!!!(来者有益)
- Oracle安装小问题
- 怎样在存储过程中根据分区名(part20050621)删除过期的分区?
- 关于oracle 中存储过程中使用 text_io的使用问题
- oracle9i下面找不到oci.h文件是怎么回事?
- Oracle中jdk的问题
- 向红星进军!!! 撒分楼!!!
- 求助:occi运行出现的getString()问题和getInt()问题
- oracle查询出现的重复记录怎么去除?
where a.columna=b.columnb
如果a和b都要column这一列,那么会报ora-00918 未明确定义列
可能要檢測相關字段是否重複,另外檢測子表或子查詢中是否存在此字段
同時建議能不用INNER JOIN就不用!
太长了 自己在sqlplus下运行 看报错在具体哪行 仔细看字段跟下面的表
ORDER BY mc.AC_ID, mr.PSTR_SEQ, clnt.clnt_class_cde, en.enty_typ, en.id_typ,
nvl(sus_ac.total,0)
--改成下面的ORDER BY mc.AC_ID, mr.PSTR_SEQ, clnt.clnt_class_cde, en.enty_typ, en.id_typ,
nvl(sus_ac.total,0)
ORA-00918: column ambiguously defined
Cause: A column name used in a join exists in more than one table and is thus referenced ambiguously. In a join, any column name that occurs in more than one of the tables must be prefixed by its table name when referenced. The column should be referenced as TABLE.COLUMN or TABLE_ALIAS.COLUMN. For example, if tables EMP and DEPT are being joined and both contain the column DEPTNO, then all references to DEPTNO should be prefixed with the table name, as in EMP.DEPTNO or E.DEPTNO.
Action: Prefix references to column names that exist in multiple tables with either the table name or a table alias and a period (.), as in the examples above.
解决办法:
因为你的列在多个表中存在,你需要在选择的列前加上表前缀以避免错误