CREATE TABLE "SFISM4"."R_SN_PARENT_DETAIL_T" ( "SERIAL_NUMBER" VARCHAR2(40), "SECTION_FLAG" VARCHAR2(12), "MO_NUMBER" VARCHAR2(25), "MODEL_NAME" VARCHAR2(25), "TYPE" VARCHAR2(5), "VERSION_CODE" VARCHAR2(10), "LINE_NAME" VARCHAR2(8), "SECTION_NAME" VARCHAR2(30), "GROUP_NAME" VARCHAR2(30), "STATION_NAME" VARCHAR2(30), "LOCATION" VARCHAR2(12), "STATION_SEQ" NUMBER(2,0), "ERROR_FLAG" VARCHAR2(1), "IN_STATION_TIME" DATE, "IN_LINE_TIME" DATE, "OUT_LINE_TIME" DATE, "SHIPPING_SN" VARCHAR2(25), "WORK_FLAG" VARCHAR2(1), "FINISH_FLAG" VARCHAR2(1), "ENC_CNT" NUMBER(2,0) DEFAULT 0, "SPECIAL_ROUTE" NUMBER(10,0), "PALLET_NO" VARCHAR2(25), "CONTAINER_NO" VARCHAR2(25), "QA_NO" VARCHAR2(50), "QA_RESULT" VARCHAR2(5), "SCRAP_FLAG" VARCHAR2(1), "NEXT_STATION" VARCHAR2(32), "CUSTOMER_NO" VARCHAR2(16), "BOM_NO" VARCHAR2(25), "BILL_NO" VARCHAR2(16), "TRACK_NO" VARCHAR2(16), "PO_NO" VARCHAR2(16), "KEY_PART_NO" VARCHAR2(25), "CARTON_NO" VARCHAR2(25), "WARRANTY_DATE" DATE, "REWORK_NO" VARCHAR2(40), "REPAIR_CNT" NUMBER(2,0), "EMP_NO" VARCHAR2(25), "PALLET_FULL_FLAG" VARCHAR2(1), "PO_LINE" VARCHAR2(16), "MACHINE1" VARCHAR2(25), "MACHINE2" VARCHAR2(25), "MACHINE3" VARCHAR2(25), "SHIP_NO" VARCHAR2(25), "INPUT_QTY1" NUMBER(14,4), "INPUT_QTY2" NUMBER(14,4), "OUTPUT_QTY1" NUMBER(14,4), "OUTPUT_QTY2" NUMBER(14,4), "INPUT_QTY3" NUMBER(14,4), "INPUT_QTY4" NUMBER(14,4), "STD_TIME" VARCHAR2(10), "REEL_SEQ" NUMBER(6,0), "CLASS" VARCHAR2(5), "CLASS_DATE" VARCHAR2(8), "WORK_SECTION" NUMBER(2,0), "PRE_STATION_TIME" DATE DEFAULT SYSDATE, "REPAIRED_STATUS_FLAG" CHAR(1) DEFAULT '0', "PARENT_GROUP_NAME" VARCHAR2(30), "WIP_LINE" VARCHAR2(8), "WIP_MACHINE" VARCHAR2(15), CONSTRAINT "R117_PARENT_MO_NN" CHECK ("MO_NUMBER" IS NOT NULL) ENABLE, CONSTRAINT "R117_PARENT_SN_NN" CHECK ("SERIAL_NUMBER" IS NOT NULL) ENABLE ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 371195904 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "RSFIS" ; CREATE INDEX "SFISM4"."R117_PARENT_EMP" ON "SFISM4"."R_SN_PARENT_DETAIL_T" ("EMP_NO") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 39845888 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "RINDX" ; CREATE INDEX "SFISM4"."R117_PARENT_GROUP" ON "SFISM4"."R_SN_PARENT_DETAIL_T" ("GROUP_NAME") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 48234496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "RINDX" ; CREATE INDEX "SFISM4"."R117_PARENT_IN_STN_TIME" ON "SFISM4"."R_SN_PARENT_DETAIL_T" ("IN_STATION_TIME") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 26214400 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "RINDX" ; CREATE INDEX "SFISM4"."R117_PARENT_LINE" ON "SFISM4"."R_SN_PARENT_DETAIL_T" ("LINE_NAME") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 27262976 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "RINDX" ; CREATE INDEX "SFISM4"."R117_PARENT_MO" ON "SFISM4"."R_SN_PARENT_DETAIL_T" ("MO_NUMBER") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 56623104 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "RINDX" ; CREATE INDEX "SFISM4"."R117_PARENT_MODEL" ON "SFISM4"."R_SN_PARENT_DETAIL_T" ("MODEL_NAME") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 49283072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "RINDX" ; CREATE INDEX "SFISM4"."R117_PARENT_PO" ON "SFISM4"."R_SN_PARENT_DETAIL_T" ("PO_NO") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 36700160 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "RINDX" ; CREATE INDEX "SFISM4"."R117_PARENT_PRE_STN_TIME" ON "SFISM4"."R_SN_PARENT_DETAIL_T" ("PRE_STATION_TIME") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 42991616 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "RINDX" ; CREATE INDEX "SFISM4"."R117_PARENT_QA" ON "SFISM4"."R_SN_PARENT_DETAIL_T" ("QA_NO") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 37748736 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "RINDX" ; CREATE INDEX "SFISM4"."R117_PARENT_SECTION" ON "SFISM4"."R_SN_PARENT_DETAIL_T" ("SECTION_NAME") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 44040192 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "RINDX" ; CREATE INDEX "SFISM4"."R117_PARENT_SN" ON "SFISM4"."R_SN_PARENT_DETAIL_T" ("SERIAL_NUMBER") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 44040192 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "RINDX" ; ALTER TABLE "SFISM4"."R_SN_PARENT_DETAIL_T" ADD CONSTRAINT "R117_PARENT_MO_NN" CHECK ("MO_NUMBER" IS NOT NULL) ENABLE; ALTER TABLE "SFISM4"."R_SN_PARENT_DETAIL_T" ADD CONSTRAINT "R117_PARENT_SN_NN" CHECK ("SERIAL_NUMBER" IS NOT NULL) ENABLE; COMMENT ON COLUMN "SFISM4"."R_SN_PARENT_DETAIL_T"."WIP_LINE" IS '實際過站線別'; COMMENT ON COLUMN "SFISM4"."R_SN_PARENT_DETAIL_T"."WIP_MACHINE" IS '實際過站機臺'; GRANT SELECT ON "SFISM4"."R_SN_PARENT_DETAIL_T" TO PUBLIC; GRANT ALTER ON "SFISM4"."R_SN_PARENT_DETAIL_T" TO "SFIS1"; GRANT DELETE ON "SFISM4"."R_SN_PARENT_DETAIL_T" TO "SFIS1"; GRANT INSERT ON "SFISM4"."R_SN_PARENT_DETAIL_T" TO "SFIS1"; GRANT SELECT ON "SFISM4"."R_SN_PARENT_DETAIL_T" TO "SFIS1"; GRANT UPDATE ON "SFISM4"."R_SN_PARENT_DETAIL_T" TO "SFIS1"; GRANT ALTER ON "SFISM4"."R_SN_PARENT_DETAIL_T" TO "R_PM"; GRANT DELETE ON "SFISM4"."R_SN_PARENT_DETAIL_T" TO "R_PM"; GRANT INSERT ON "SFISM4"."R_SN_PARENT_DETAIL_T" TO "R_PM"; GRANT SELECT ON "SFISM4"."R_SN_PARENT_DETAIL_T" TO "R_PM"; GRANT UPDATE ON "SFISM4"."R_SN_PARENT_DETAIL_T" TO "R_PM"; GRANT UPDATE ON "SFISM4"."R_SN_PARENT_DETAIL_T" TO "R_REPAIR"; GRANT UPDATE ON "SFISM4"."R_SN_PARENT_DETAIL_T" TO "R_PACK1"; GRANT UPDATE ON "SFISM4"."R_SN_PARENT_DETAIL_T" TO "R_PACK2"; GRANT ALTER ON "SFISM4"."R_SN_PARENT_DETAIL_T" TO "R_CQC"; GRANT DELETE ON "SFISM4"."R_SN_PARENT_DETAIL_T" TO "R_CQC"; GRANT INSERT ON "SFISM4"."R_SN_PARENT_DETAIL_T" TO "R_CQC"; GRANT SELECT ON "SFISM4"."R_SN_PARENT_DETAIL_T" TO "R_CQC"; GRANT UPDATE ON "SFISM4"."R_SN_PARENT_DETAIL_T" TO "R_CQC"; GRANT ALTER ON "SFISM4"."R_SN_PARENT_DETAIL_T" TO "R_SHIP"; GRANT DELETE ON "SFISM4"."R_SN_PARENT_DETAIL_T" TO "R_SHIP"; GRANT INSERT ON "SFISM4"."R_SN_PARENT_DETAIL_T" TO "R_SHIP"; GRANT SELECT ON "SFISM4"."R_SN_PARENT_DETAIL_T" TO "R_SHIP"; GRANT UPDATE ON "SFISM4"."R_SN_PARENT_DETAIL_T" TO "R_SHIP"; GRANT UPDATE ON "SFISM4"."R_SN_PARENT_DETAIL_T" TO "R_SMO"; GRANT UPDATE ON "SFISM4"."R_SN_PARENT_DETAIL_T" TO "EEMES"; select * from (select detailt.serial_number,detailt.mo_number,detailt.line_name,detailt.class_date, detailt.DA,sumt1.sumqty summaryDA,detailt.ALBOND,0 summaryALBOND,detailt.COATING, 0 summaryCOATING,detailt.TESTING,0 summaryTESTING, detailt.COBA修理,0 summaryCOBA修理 from (select serial_number,mo_number,line_name,class_date,parent_group_name, CASE WHEN(parent_group_name = 'DA') THEN output_qty2 ELSE 0 END DA, CASE WHEN(parent_group_name = 'AL BOND') THEN output_qty2 ELSE 0 END ALBOND, CASE WHEN(parent_group_name = 'COATING') THEN output_qty2 ELSE 0 END COATING, CASE WHEN(parent_group_name = 'TESTING') THEN output_qty2 ELSE 0 END TESTING, CASE WHEN(parent_group_name = 'COBA修理') THEN output_qty2 ELSE 0 END COBA修理 from SFISM4.r_sn_parent_detail_t rsn where mo_number='110008064TI30XIISE-COBA' and parent_group_name = 'DA') detailt, (select mo_number, parent_group_name,sum(output_qty2) sumqty from SFISM4.r_sn_parent_detail_t rsn where mo_number='110008064TI30XIISE-COBA' and parent_group_name = 'DA' group by rsn.parent_group_name,mo_number) sumt1 where detailt.mo_number(+) =sumt1.mo_number and detailt.parent_group_name(+) =sumt1.parent_group_name union all select detailt.serial_number,detailt.mo_number,detailt.line_name,detailt.class_date, detailt.DA,0 summaryDA,detailt.ALBOND,sumt1.sumqty summaryALBOND,detailt.COATING, 0 summaryCOATING,detailt.TESTING,0 summaryTESTING, detailt.COBA修理,0 summaryCOBA修理 from (select serial_number,mo_number,line_name,class_date,parent_group_name, CASE WHEN(parent_group_name = 'DA') THEN output_qty2 ELSE 0 END DA, CASE WHEN(parent_group_name = 'AL BOND') THEN output_qty2 ELSE 0 END ALBOND, CASE WHEN(parent_group_name = 'COATING') THEN output_qty2 ELSE 0 END COATING, CASE WHEN(parent_group_name = 'TESTING') THEN output_qty2 ELSE 0 END TESTING, CASE WHEN(parent_group_name = 'COBA修理') THEN output_qty2 ELSE 0 END COBA修理 from SFISM4.r_sn_parent_detail_t rsn where mo_number='110008064TI30XIISE-COBA' and parent_group_name = 'AL BOND') detailt, (select mo_number, parent_group_name,sum(output_qty2) sumqty from SFISM4.r_sn_parent_detail_t rsn where mo_number='110008064TI30XIISE-COBA' and parent_group_name = 'AL BOND' group by rsn.parent_group_name,mo_number) sumt1 where detailt.mo_number(+) =sumt1.mo_number and detailt.parent_group_name(+) =sumt1.parent_group_name union all select detailt.serial_number,detailt.mo_number,detailt.line_name,detailt.class_date, detailt.DA,0 summaryDA,detailt.ALBOND,0 summaryALBOND,detailt.COATING, sumt1.sumqty summaryCOATING,detailt.TESTING,0 summaryTESTING, detailt.COBA修理,0 summaryCOBA修理 from (select serial_number,mo_number,line_name,class_date,parent_group_name, CASE WHEN(parent_group_name = 'DA') THEN output_qty2 ELSE 0 END DA, CASE WHEN(parent_group_name = 'AL BOND') THEN output_qty2 ELSE 0 END ALBOND, CASE WHEN(parent_group_name = 'COATING') THEN output_qty2 ELSE 0 END COATING, CASE WHEN(parent_group_name = 'TESTING') THEN output_qty2 ELSE 0 END TESTING, CASE WHEN(parent_group_name = 'COBA修理') THEN output_qty2 ELSE 0 END COBA修理 from SFISM4.r_sn_parent_detail_t rsn where mo_number='110008064TI30XIISE-COBA' and parent_group_name = 'COATING') detailt, (select mo_number, parent_group_name,sum(output_qty2) sumqty from SFISM4.r_sn_parent_detail_t rsn where mo_number='110008064TI30XIISE-COBA' and parent_group_name = 'COATING' group by rsn.parent_group_name,mo_number) sumt1 where detailt.mo_number(+) =sumt1.mo_number and detailt.parent_group_name(+) =sumt1.parent_group_name union all select detailt.serial_number,detailt.mo_number,detailt.line_name,detailt.class_date, detailt.DA,0 summaryDA,detailt.ALBOND,0 summaryALBOND,detailt.COATING, 0 summaryCOATING,detailt.TESTING,sumt1.sumqty summaryTESTING, detailt.COBA修理,0 summaryCOBA修理 from (select serial_number,mo_number,line_name,class_date,parent_group_name, CASE WHEN(parent_group_name = 'DA') THEN output_qty2 ELSE 0 END DA, CASE WHEN(parent_group_name = 'AL BOND') THEN output_qty2 ELSE 0 END ALBOND, CASE WHEN(parent_group_name = 'COATING') THEN output_qty2 ELSE 0 END COATING, CASE WHEN(parent_group_name = 'TESTING') THEN output_qty2 ELSE 0 END TESTING, CASE WHEN(parent_group_name = 'COBA修理') THEN output_qty2 ELSE 0 END COBA修理 from SFISM4.r_sn_parent_detail_t rsn where mo_number='110008064TI30XIISE-COBA' and parent_group_name = 'TESTING') detailt, (select mo_number, parent_group_name,sum(output_qty2) sumqty from SFISM4.r_sn_parent_detail_t rsn where mo_number='110008064TI30XIISE-COBA' and parent_group_name = 'TESTING' group by rsn.parent_group_name,mo_number) sumt1 where detailt.mo_number(+) =sumt1.mo_number and detailt.parent_group_name(+) =sumt1.parent_group_name union all select detailt.serial_number,detailt.mo_number,detailt.line_name,detailt.class_date, detailt.DA,0 summaryDA,detailt.ALBOND,0 summaryALBOND,detailt.COATING, 0 summaryCOATING,detailt.TESTING,0 summaryTESTING, detailt.COBA修理,sumt1.sumqty summaryCOBA修理 from (select serial_number,mo_number,line_name,class_date,parent_group_name, CASE WHEN(parent_group_name = 'DA') THEN output_qty2 ELSE 0 END DA, CASE WHEN(parent_group_name = 'AL BOND') THEN output_qty2 ELSE 0 END ALBOND, CASE WHEN(parent_group_name = 'COATING') THEN output_qty2 ELSE 0 END COATING, CASE WHEN(parent_group_name = 'TESTING') THEN output_qty2 ELSE 0 END TESTING, CASE WHEN(parent_group_name = 'COBA修理') THEN output_qty2 ELSE 0 END COBA修理 from SFISM4.r_sn_parent_detail_t rsn where mo_number='110008064TI30XIISE-COBA' and parent_group_name = 'COBA修理') detailt, (select mo_number, parent_group_name,sum(output_qty2) sumqty from SFISM4.r_sn_parent_detail_t rsn where mo_number='110008064TI30XIISE-COBA' and parent_group_name = 'COBA修理' group by rsn.parent_group_name,mo_number) sumt1 where detailt.mo_number(+) =sumt1.mo_number and detailt.parent_group_name(+) =sumt1.parent_group_name) allt order by serial_number,class_date --select distinct parent_group_name from SFISM4.r_sn_parent_detail_t where mo_number='110008064TI30XIISE-COBA' --select * from SFISM4.r_sn_parent_detail_t rsn where mo_number='110008064TI30XIISE-COBA' order by serial_number[@more@]

first oracle sql

相關文章