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;