大家帮忙看下:
SQL> SET TIMING ON
SQL> SET AUTOTRACE TRACEONLY
SQL> SELECT A.REGR "Factory",
2 A.ORDNO "Sale Order Code",
3 replace(replace(replace(E.REF2, ' ', ''), chr(10), ''), chr(13), '') CUST_PO#,
4 replace(replace(replace(B.CTRPTYREF, ' ', ''), chr(10), ''),
5 chr(13),
6 '') CUST_STYLE,
7 PG.LNAME || ' ' || PG1.LNAME "Category",
8 A.CTRPTY "Customer code",
9 MAX(CTR.SNAME) "Customer",
10 DECODE(I.CODE, 'I', 'HANG', NULL, 'UNKNOW', 'BOX') "Container_Type",
11 J.LNAME SHIP_MODE,
12 ROUND(CEIL(SUM(H.QTY) / D.BOX_QTY) * I.CBM, 2) "CBM(M ^ 3)",
13 SUM(H.QTY) QTY
14 FROM ORD A,
15 ORDITM B,
16 ORDITMCL C,
17 ORDITMSZ D,
18 ORDSCH E,
19 BL F,
20 BLDTL G,
21 BLPCKDTL H,
22 CONTY I,
23 METHOD J,
24 PRD P,
25 PRDGP PG,
26 PRDGP PG1,
27 CTRPTY CTR
28 WHERE A.ORDTY = 'S'
29 AND A.STFG IN ('A', 'C')
30 AND CTR.CODE = A.CTRPTY
31 -- AND A.CTRPTY LIKE 'DSL%'
32 AND B.ORDSEQ = A.ORDSEQ
33 AND C.ORDSEQ = B.ORDSEQ
34 AND C.ITMNO = B.ITMNO
35 AND D.ORDSEQ = B.ORDSEQ
36 AND D.ITMNO = B.ITMNO
37 AND P.CODE(+) = B.PRD
38 AND PG.CODE(+) = P.GRP
39 AND PG1.CODE(+) = P.GRP1
40 AND E.ORDSEQ = C.ORDSEQ
41 AND NEXT_DAY(TRUNC(E.SCHDT - 1), '星期三') =
42 NEXT_DAY(TRUNC(SYSDATE - 1), '星期三')
43 AND F.TYPE = 'SHPAD'
44 AND F.STFG IN ('A', 'C')
45 AND G.BLSEQ = F.BLSEQ
46 AND G.ORDSEQ = A.ORDSEQ
47 AND G.ITMNO = B.ITMNO
48 AND G.SCHNO = E.SCHNO
49 AND H.BLSEQ = F.BLSEQ
50 AND H.REFNO = G.REFNO
51 AND H.ORDSEQ = G.ORDSEQ
52 AND H.ITMNO = G.ITMNO
53 AND H.SHPNO = G.SHPNO
54 AND H.ORDSEQ = A.ORDSEQ
55 AND H.ITMNO = B.ITMNO
56 AND H.CLNO = C.CLNO
57 AND H.SZNO = D.SZNO
58 AND I.CODE = D.CONTY
59 AND J.CODE = E.METHOD
60 GROUP BY A.CTRPTY,
61 A.ORDNO,
62 A.REGR,
63 PG.LNAME || ' ' || PG1.LNAME,
64