第一次见到这么长的SQL语句

来自一个ERP系统,运行时间越为5分钟,但用户希望是10秒以内.

不过还是很佩服作者的,

过几天才有时间考虑 .

SELECT X.ORGANIZATION_CODE,
       X.compeq_lot_num real_lot_num,
       decode(x.wip_type, 2, 'U-' || nvl(y.previous_compeq_lot, X.COMPEQ_LOT_NUM), X.COMPEQ_LOT_NUM) compeq_lot_num,
       X.organization_code_mf,
       X.next_organization_code_mf,
       X.PART_NUM,
       X.PROD_TYPE,
       X.PRINT_FLAG,
       X.JOB_STATUS,
       X.JOB_STATUS_TYPE,
       X.JOB_TYPE,
       X.WIP_TYPE,
       X.CONTROL_FLAG,
       X.TOP_URGENT_FLAG,
       X.FOR_CUSTOMER,
       DECODE(X.FOR_CUSTOMER, 1, '(?)', '2', '(称)', '') FOR_CUSTOMER_NAME,
       nvl(X.work_film_id, 0) WORK_FILM_ID,
       nvl(X.tool_id, 0) tool_ID,
       nvl(X.program_id, 0) program_ID,
       NVL(X.DATE_FOR_DELIVERY, 0) DATE_FOR_DELIVERY,
       decode(X.DATE_FOR_DELIVERY, 1, 'aaa', ' ') commit_lot,
       X.ISSUE_TYPE,
       X.ISSUE_mark,
       X.CURRENT_LOT_CONTROL_SEQ_NUM,
       X.DEPARTMENT,
       X.PROCESS_SEQUENCE,
       X.ALLOW_SPLIT_AND_MERGE,
       X.CURRENT_DEPARTMENT_CODE,
       X.CURRENT_MOVE_OP_SEQ_NUM,
       TO_CHAR(X.DATE_ISSUED, 'MM/DD HH24:MI') DATE_ISSUED,
       TO_CHAR(X.DATE_NEEDED, 'MM/DD HH24:MI') DATE_NEEDED,
       TO_CHAR(X.DATE_COMPLETED, 'MM/DD HH24:MI') DATE_COMPLETED,
       TO_CHAR(X.DATE_STOCK_IN, 'MM/DD HH24:MI') DATE_STOCK_IN,
       NVL(X.DR_VALUE, 999.99) DR_VALUE,
       X.PIECES_IN_PANEL,
       X.SUBPIECES_IN_PIECES,
       X.LAST_UPDATE_DATE,
       X.delay_hours,
       X.PREVIOUS_OPERATION_SEQ_NUM,
       X.NEXT_OPERATION_SEQ_NUM,
       X.OP_STATUS,
       X.OPERATION_STATUS,
       TO_CHAR(X.IN_PROCESS_TIME, 'MM/DD HH24:MI') IN_PROCESS_TIME,
       TO_CHAR(X.OUT_PROCESS_TIME, 'MM/DD HH24:MI') OUT_PROCESS_TIME,
       TO_CHAR(X.SCHEDULED_OUT_PROCESS_TIME, 'MM/DD HH24:MI') SCHEDULED_OUT_PROCESS_TIME,
       X.is_rework,
       DECODE(X.is_rework, 'Y', 'R', '') IS_R,
       X.dec_WRWK_rn,
       X.FINISH_LOT_CONTROL,
       X.out_source_id,
       X.HOLD_ID,
       X.vendor_num,
       X.LOT_STAMP,
       X.JOB_TYPE_NAME,
       X.JOB_COLOR,
       X.CONTROL_FLAG_NAME,
       X.ISSUE_NAME,
       X.TOP_URGENT_NAME,
       X.NEXT_DEPARTMENT_CODE,
       X.NEXT_IS_REWORK,
       DECODE(X.NEXT_IS_REWORK, 'Y', 'R', '') NEXT_R,
       X.NEXT_WRWK_RN,
       X.GRADE,
       NVL(y.lam_num, 0) LAM_NUM,
       SUM(nvl(y.PANEL_BALANCE_UNSTOCK, 0)) PANEL_BALANCE_UNSTOCK,
       SUM(nvl(y.PANEL_BALANCE, 0)) PANEL_BALANCE,
       SUM(nvl(y.PANEL_COMPLETED, 0)) PANEL_COMPLETED,
       X.DR_LIMIT,
       X.JOB_STATUS_NEW,
       decode(X.for_whom, null, 'aaa', '::', 'aaa', 'aaa?:' || X.for_whom) for_whom,
       LOT_DATE_CODE,
       AW_TOOL_ID,
       AW_TOOL_ID2
  FROM (SELECT A.*,
               C.DEPARTMENT,
               C.PROCESS_SEQUENCE,
               C.ALLOW_SPLIT_AND_MERGE,
               NVL(round((F.Scheduled_out_process_time - sysdate) * 24, 1), 0) delay_hours,
               nvl(F.organization_code_mf, F.organization_code) organization_code_mf,
               F.PREVIOUS_OPERATION_SEQ_NUM,
               F.NEXT_OPERATION_SEQ_NUM,
               F.OP_STATUS,
               F.OPERATION_STATUS,
               F.IN_PROCESS_TIME,
               F.OUT_PROCESS_TIME,
               F.SCHEDULED_OUT_PROCESS_TIME,
               f.is_rework,
               f.dec_WRWK_rn,
               F.FINISH_LOT_CONTROL,
               F.out_source_id,
               nvl(F.HOLD_id, 0) hold_id,
               E.MEANING_job JOB_TYPE_NAME,
               e.MEANING_color JOB_COLOR,
               H.MEANING CONTROL_FLAG_NAME,
               I.MEANING ISSUE_NAME,
               J.MEANING JOB_STATUS_name,
               J.ATTRIBUTE1 JOB_STATUS_TYPE,
               e.top_urgent_name,
               nvl(L.organization_code_mf, L.organization_code) next_organization_code_mf,
               NVL(L.DEPARTMENT_CODE, '*') NEXT_DEPARTMENT_CODE,
               L.IS_REWORK NEXT_IS_REWORK,
               L.DEC_WRWK_RN NEXT_WRWK_RN,
               to_char(k.layer_count, '00') || ' ' || lpad(k.lam_kind, 1, '*') || lpad(k.gold_finger_flag, 1, '*') || lpad(k.sold_mask_kind, 1, '*') || lpad(k.silk_screen_print_flag, 1, '*') || lpad(k.mfg_kind, 3, '*') GRADE,
               NVL(C.DR_LIMIT, 499.99) DR_LIMIT,
               A.JOB_STATUS || ',' || J.MEANING || ',' || J.ATTRIBUTE1 || ',' || A.WIP_TYPE || ',' || A.DR_VALUE || ',' || F.FINISH_LOT_CONTROL || ',' || NVL(F.HOLD_ID, 0) || ',' || NVL(A.WORK_FILM_ID, 0) || ',' || NVL(A.TOOL_ID, 0) || ',' || NVL(A.PROGRAM_ID, 0) || ',' || TO_CHAR(NVL(C.DR_LIMIT, 0)) JOB_STATUS_NEW
          FROM CM_WIP_LOt A,
               CM_WIP_OPERATION F,
               cm_work_processes_PPC c,
               cm_wip_job_color_v E,
               CM_MFG_LOOKUPS_new H,
               CM_MFG_LOOKUPS_new I,
               CM_MFG_LOOKUPS_new J,
               CM_WIP_OPERATION L,
               CM_bom_partnum k
         where A.ORGANIZATION_CODE_MF = 'CC'
           AND A.ORGANIZATION_CODE = 'CC'
           AND A.CURRENT_DEPARTMENT_CODE in ('23')
           AND A.WIP_TYPE IN (1,
               2)
           AND A.JOB_STATUS IN ('1',
               '14',
               '2')
          
           AND A.ORGANIZATION_CODE = F.ORGANIZATION_CODE (+)
           AND A.COMPEQ_LOT_NUM = F.COMPEQ_LOT_NUM (+)
           AND A.CURRENT_LOT_CONTROL_SEQ_NUM = F.OPERATION_SEQ_NUM (+)
           and a.organization_code_MF = C.organization_code
           and a.CURRENT_DEPARTMENT_CODE = C.PROCESS_CODE
           AND c.department in ('Q92')
           AND A.ORGANIZATION_CODE = E.ORGANIZATION_CODE (+)
           AND A.JOB_TYPE = E.LOOKUP_CODE (+)
           AND E.LOOKUP_TYPE (+) = 'JOB_TYPE'
           AND A.ORGANIZATION_CODE = H.ORGANIZATION_CODE
           AND A.CONTROL_FLAG = H.LOOKUP_CODE
           AND H.LOOKUP_TYPE = 'CONTROL_FLAG'
           AND A.ORGANIZATION_CODE = I.ORGANIZATION_CODE
           AND A.ISSUE_MARK = I.LOOKUP_CODE
           AND I.LOOKUP_TYPE = 'ISSUE_MARK'
           AND A.ORGANIZATION_CODE = J.ORGANIZATION_CODE
           AND A.JOB_STATUS = J.LOOKUP_CODE
           AND J.LOOKUP_TYPE = 'JOB_STATUS'
           AND F.ORGANIZATION_CODE = L.ORGANIZATION_CODE (+)
           AND F.COMPEQ_LOT_NUM = L.COMPEQ_LOT_NUM (+)
           AND F.NEXT_OPERATION_SEQ_NUM = L.OPERATION_SEQ_NUM (+)
           AND a.ORGANIZATION_CODE = k.ORGANIZATION_CODE
           AND a.part_num = k.part_num) X,
       cm_wip_to_oracle_job y
 where x.ORGANIZATION_CODE = y.ORGANIZATION_CODE (+)
   AND x.COMPEQ_LOT_NUM = y.COMPEQ_LOT_NUM (+)
   AND y.ORGANIZATION_CODE = 'CC'
   AND y.panel_balance > 0
 GROUP BY X.ORGANIZATION_CODE,
          X.compeq_lot_num,
          decode(x.wip_type, 2, 'U-' || nvl(y.previous_compeq_lot, X.COMPEQ_LOT_NUM), X.COMPEQ_LOT_NUM),
          X.organization_code_mf,
          X.next_organization_code_mf,
          X.PART_NUM,
          X.PROD_TYPE,
          X.PRINT_FLAG,
          X.JOB_STATUS,
          X.JOB_STATUS_TYPE,
          X.JOB_TYPE,
          X.WIP_TYPE,
          X.CONTROL_FLAG,
          X.TOP_URGENT_FLAG,
          X.FOR_CUSTOMER,
          DECODE(X.FOR_CUSTOMER, 1, '(?)', '2', '(称)', ''),
          nvl(X.work_film_id, 0),
          nvl(X.tool_id, 0),
          nvl(X.program_id, 0),
          NVL(X.DATE_FOR_DELIVERY, 0),
          decode(X.DATE_FOR_DELIVERY, 1, 'aaa', ' '),
          X.ISSUE_TYPE,
          X.ISSUE_mark,
          X.CURRENT_LOT_CONTROL_SEQ_NUM,
          X.DEPARTMENT,
          X.PROCESS_SEQUENCE,
          X.ALLOW_SPLIT_AND_MERGE,
          X.CURRENT_DEPARTMENT_CODE,
          X.CURRENT_MOVE_OP_SEQ_NUM,
          TO_CHAR(X.DATE_ISSUED, 'MM/DD HH24:MI'),
          TO_CHAR(X.DATE_NEEDED, 'MM/DD HH24:MI'),
          TO_CHAR(X.DATE_COMPLETED, 'MM/DD HH24:MI'),
          TO_CHAR(X.DATE_STOCK_IN, 'MM/DD HH24:MI'),
          X.DR_VALUE,
          X.PIECES_IN_PANEL,
          X.SUBPIECES_IN_PIECES,
          X.LAST_UPDATE_DATE,
          X.delay_hours,
          X.PREVIOUS_OPERATION_SEQ_NUM,
          X.NEXT_OPERATION_SEQ_NUM,
          X.OP_STATUS,
          X.OPERATION_STATUS,
          TO_CHAR(X.IN_PROCESS_TIME, 'MM/DD HH24:MI'),
          TO_CHAR(X.OUT_PROCESS_TIME, 'MM/DD HH24:MI'),
          TO_CHAR(X.SCHEDULED_OUT_PROCESS_TIME, 'MM/DD HH24:MI'),
          X.is_rework,
          DECODE(X.is_rework, 'Y', 'R', ''),
          X.dec_WRWK_rn,
          X.FINISH_LOT_CONTROL,
          X.out_source_id,
          X.HOLD_ID,
          X.vendor_num,
          X.LOT_STAMP,
          X.JOB_TYPE_NAME,
          X.JOB_COLOR,
          X.CONTROL_FLAG_NAME,
          X.ISSUE_NAME,
          X.TOP_URGENT_NAME,
          X.NEXT_DEPARTMENT_CODE,
          X.NEXT_IS_REWORK,
          DECODE(X.NEXT_IS_REWORK, 'Y', 'R', ''),
          X.NEXT_WRWK_RN,
          X.GRADE,
          NVL(y.lam_num, 0),
          X.DR_LIMIT,
          X.JOB_STATUS_NEW,
          decode(X.for_whom, null, 'aaa', '::', 'aaa', 'aaaa:' || X.for_whom),
          LOT_DATE_CODE,
          AW_TOOL_ID,
          AW_TOOL_ID2
 ORDER BY X.PART_NUM,
          X.CURRENT_DEPARTMENT_CODE,
          X.DR_VALUE,
          decode(x.wip_type, 2, 'U-' || nvl(y.previous_compeq_lot, X.COMPEQ_LOT_NUM), X.COMPEQ_LOT_NUM),
          NVL(y.lam_num, 0)
 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/936/viewspace-60505/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/936/viewspace-60505/