ORA-00918 未明确定义列

oracle sql语句中有子查询，报错未明确定义列的原因：

1.子查询中有相同的列名

2.子查询需要重命名为新的表

select * from (select * from XXXX where sr_no='xxx') t

3.如果涉及到了类型转换函数或者非null判断函数，最好能把该列重命名

select * from (

select 

cast(nvl(sr_no,0) as number) srno ----重命名处

from XXXX where sr_no='xxx') t

 

SELECT * FROM
(
SELECT cast(nvl(T2.SR_NO,0) as number) as srno,
T1.UNIT_RATE,T1.ITEM_CODE as Excel_itemcode,T2.ITEM_CODE,
T1.MATL_SIZE as Excel_matlsize,T2.MATL_SIZE,
T1.QUANTITY,T2.QTY,
T1.THICKNESS as Excel_thickness,T2.THICKNESS,
T1.TAG_NO as Excel_tagno,T2.TAG_NO
FROM TEMP_PO_IMPORT_EXCEL T1
LEFT JOIN MR_D T2
ON T1.ITEM_CODE=T2.ITEM_CODE
AND T1.MATL_SIZE=T2.MATL_SIZE
--AND T1.SPL_COMMODITY_CODE=T2.SPL_COMMODITY_CODE
       ----启用null为空设置为0的条件
--AND nvl(T1.SPL_COMMODITY_CODE,0)=nvl(T2.SPL_COMMODITY_CODE,0) 
AND T1.QUANTITY=T2.QTY
AND T2.MR_NO='BXHJ001'
AND T2.SPL_COMMODITY_CODE IS NULL
AND T1.SPL_COMMODITY_CODE IS NULL
order by T2.SR_NO
) t
where t.srno=0;

 

转载于:https://www.cnblogs.com/minglilee2012/p/3571271.html