select语句中select部分提示“未明确定义列”，无解

Question

SELECT * FROM 
(
      SELECT  LOT.OBJECT_ID, 
              LOT.CUSTOMER_NAME, 
              LOT.PKG, 
              LOT.PART_NAME, 
              LOT.LOT_ID, 
              LOT.WAFER_LOT_ID,
              LOT.CUSTOMER_ORDER, 
              PART.PART_NAME4 AS CUST_SHIPNAME,
              LOT.START_MAIN_QTY, 
              LOT.DATE_CODE,
              LOT.START_TIME,
              LOT.UPDATED,
              LOT.START_SUB_QTY,
             (SELECT SUB_QTY  FROM WIPHIS_LOT WHERE HISTORY_SEQ = 
               (
                  SELECT MAX(HIS.HISTORY_SEQ)
                    FROM WIPHIS_LOT HIS,WF_PROCESSDEFINITION STEP 
                      WHERE HIS.STEP_ID = STEP.OBJECT_ID 
                        AND HIS.LOT_ID= LOT.LOT_ID 
                        AND STEP.COMPLETE_TYPE ='TEST')
              ) 
                   AS TEST,
               LOT.SUB_QTY 
                   AS TEST_OUT,
               (TRUNC
                 ((LOT.SUB_QTY / LOT.START_SUB_QTY),4)*100
               ) 
                   AS YIELD,
               (LOT.START_SUB_QTY - LOT.SUB_QTY) 
                   AS TOL_REJ 
      FROM   WIP_LOT LOT,
             PRD_PART PART
      WHERE  LOT.PART_ID=PART.OBJECT_ID   
               AND LOT.OBJECT_ID IN
      (
           SELECT DISTINCT HIS.LOT_OBJECT_ID 
            FROM WIPHIS_LOT HIS,
                 AD_ORG ORG 
             WHERE HIS.TRANS_TYPE = 'SHIPLOT'
               AND HIS.AD_ORG_ID = ORG.AD_ORG_ID 
              AND HIS.SUB_QTY > 0
               AND ORG.NAME IN ('30-TEST')
           UNION
         SELECT DISTINCT HIS.LOT_OBJECT_ID 
           FROM WIPHIS_LOT HIS,
                AD_ORG ORG 
             WHERE  HIS.COM_CLASS = 'WIP'
               AND HIS.AD_ORG_ID = ORG.AD_ORG_ID
               AND HIS.SUB_QTY > 0
                AND ORG.NAME IN ('30-TEST')
      ) 
      ) T1  left join 
      (        
      SELECT HIS.LOT_OBJECT_ID, 
             SBC.ACTION_CODE, 
             SBC.SUB_QTY,
             HIS.TRANS_TYPE,
             HIS.HISTORY_SEQ,
             HIS.SUB_QTY,
             HIS.STEP_NAME
      FROM WIPHIS_LOT HIS,WIPHIS_SBD SBC
      WHERE SBC.HISTORY_ID = HIS.OBJECT_ID  
        AND HIS.LOT_OBJECT_ID IN 
          (
             SELECT DISTINCT ORG.OBJECT_ID 
               FROM WIPHIS_LOT HIS,
                     AD_ORG ORG 
                WHERE HIS.TRANS_TYPE = 'SHIPLOT'
                  AND HIS.AD_ORG_ID = ORG.AD_ORG_ID
                  AND HIS.SUB_QTY > 0
                  AND ORG.NAME IN ('30-TEST')
            UNION
            SELECT DISTINCT ORG.OBJECT_ID 
                FROM WIPHIS_LOT HIS,
                    AD_ORG ORG 
                WHERE  HIS.COM_CLASS = 'WIP'
                  AND HIS.AD_ORG_ID = ORG.AD_ORG_ID
                  AND HIS.SUB_QTY > 0
                  AND ORG.NAME IN ('30-TEST')
          )    
      ) T2  
      ON  T1.OBJECT_ID = T2.LOT_OBJECT_ID   
        ORDER BY T2.LOT_OBJECT_ID,T2.HISTORY_SEQ;      
      
在plsql developer中运行时，select处有红色波浪线提示，运行后提示“未明确定义列”。
原因不清楚。请大神们指教。

Answer 1

SELECT * FROM 
(
      SELECT  LOT.OBJECT_ID, 
              LOT.CUSTOMER_NAME, 
              LOT.PKG, 
              LOT.PART_NAME, 
              LOT.LOT_ID, 
              LOT.WAFER_LOT_ID,
              LOT.CUSTOMER_ORDER, 
              PART.PART_NAME4 AS CUST_SHIPNAME,
              LOT.START_MAIN_QTY, 
              LOT.DATE_CODE,
              LOT.START_TIME,
              LOT.UPDATED,
              LOT.START_SUB_QTY,
             (SELECT SUB_QTY  FROM WIPHIS_LOT WHERE HISTORY_SEQ = 
               (
                  SELECT MAX(HIS.HISTORY_SEQ)
                    FROM WIPHIS_LOT HIS,WF_PROCESSDEFINITION STEP 
                      WHERE HIS.STEP_ID = STEP.OBJECT_ID 
                        AND HIS.LOT_ID= LOT.LOT_ID 
                        AND STEP.COMPLETE_TYPE ='TEST')
              ) 
                   AS TEST,
               LOT.SUB_QTY 
                   AS TEST_OUT,
               (TRUNC
                 ((LOT.SUB_QTY / LOT.START_SUB_QTY),4)*100
               ) 
                   AS YIELD,
               (LOT.START_SUB_QTY - LOT.SUB_QTY) 
                   AS TOL_REJ 
      FROM   WIP_LOT LOT,
             PRD_PART PART
      WHERE  LOT.PART_ID=PART.OBJECT_ID   
               AND LOT.OBJECT_ID IN
      (
           SELECT DISTINCT HIS.LOT_OBJECT_ID 
            FROM WIPHIS_LOT HIS,
                 AD_ORG ORG 
             WHERE HIS.TRANS_TYPE = 'SHIPLOT'
               AND HIS.AD_ORG_ID = ORG.AD_ORG_ID 
              AND HIS.SUB_QTY > 0
               AND ORG.NAME IN ('30-TEST')
           UNION
         SELECT DISTINCT HIS.LOT_OBJECT_ID 
           FROM WIPHIS_LOT HIS,
                AD_ORG ORG 
             WHERE  HIS.COM_CLASS = 'WIP'
               AND HIS.AD_ORG_ID = ORG.AD_ORG_ID
               AND HIS.SUB_QTY > 0
                AND ORG.NAME IN ('30-TEST')
      ) 
      ) T1  left join 
      (        
      SELECT HIS.LOT_OBJECT_ID, 
             SBC.ACTION_CODE, 
             SBC.SUB_QTY,
             HIS.TRANS_TYPE,
             HIS.HISTORY_SEQ,
             HIS.SUB_QTY,
             HIS.STEP_NAME
      FROM WIPHIS_LOT HIS,WIPHIS_SBD SBC
      WHERE SBC.HISTORY_ID = HIS.OBJECT_ID  
        AND HIS.LOT_OBJECT_ID IN 
          (
             SELECT DISTINCT ORG.OBJECT_ID 
               FROM WIPHIS_LOT HIS,
                     AD_ORG ORG 
                WHERE HIS.TRANS_TYPE = 'SHIPLOT'
                  AND HIS.AD_ORG_ID = ORG.AD_ORG_ID
                  AND HIS.SUB_QTY > 0
                  AND ORG.NAME IN ('30-TEST')
            UNION
            SELECT DISTINCT ORG.OBJECT_ID 
                FROM WIPHIS_LOT HIS,
                    AD_ORG ORG 
                WHERE  HIS.COM_CLASS = 'WIP'
                  AND HIS.AD_ORG_ID = ORG.AD_ORG_ID
                  AND HIS.SUB_QTY > 0
                  AND ORG.NAME IN ('30-TEST')
          )    
      ) T2  
      ON  T1.OBJECT_ID = T2.LOT_OBJECT_ID   
        ORDER BY T2.LOT_OBJECT_ID,T2.HISTORY_SEQ;      
      你主查询里是查询所有的列，但是我标注的绿色部分，列名是重复的，你把其中一个起个别名就行了

Answer 2

这个就是你业务的问题了，你可以试试单独T1、T2查询下看有数据没？让后看看where的限制条件说明的。问题是需要独立解决的，尤其还不是技术上的问题了。