/* Formatted on 2011/04/21 14:25 (Formatter Plus v4.8.8) */
SELECT plant, '2011' cyear,
CASE
WHEN plant IS NULL
THEN '全部合計'
WHEN plant IS NOT NULL AND cust IS NULL
THEN '(' || plant || ')' || '小計'
ELSE cust
END x,
SUM (d01n) x1a, SUM (d01p) x1b, SUM (d01px) x1c, SUM (d02n) x2a,
SUM (d02p) x2b, SUM (d02px) x2c, SUM (d03n) x3a, SUM (d03p) x3b,
SUM (d03px) x3c, SUM (d04n) x4a, SUM (d04p) x4b, SUM (d04px) x4c,
SUM (d05n) x5a, SUM (d05p) x5b, SUM (d05px) x5c, SUM (d06n) x6a,
SUM (d06p) x6b, SUM (d06px) x6c, SUM (d07n) x7a, SUM (d07p) x7b,
SUM (d07px) x7c, SUM (d08n) x8a, SUM (d08p) x8b, SUM (d08px) x8c,
SUM (d09n) x9a, SUM (d09p) x9b, SUM (d09px) x9c, SUM (d10n) x10a,
SUM (d10p) x10b, SUM (d10px) x10c, SUM (d11n) x11a, SUM (d11p) x11b,
SUM (d11px) x11c, SUM (d12n) x12a, SUM (d12p) x12b, SUM (d12px) x12c,
SUM (dsumn) xa, SUM (dsump) xb,
SUM ( NVL (d01px, 0)
+ NVL (d02px, 0)
+ NVL (d03px, 0)
+ NVL (d04px, 0)
+ NVL (d05px, 0)
+ NVL (d06px, 0)
+ NVL (d07px, 0)
+ NVL (d08px, 0)
+ NVL (d09px, 0)
+ NVL (d10px, 0)
+ NVL (d11px, 0)
+ NVL (d12px, 0)
) xc
FROM (SELECT plant, cust, t1.curr, d01n, d01p, (d01p * r1) d01px, d02n,
d02p, (d02p * r2) d02px, d03n, d03p, (d03p * r3) d03px, d04n,
d04p, (d04p * r4) d04px, d05n, d05p, (d05p * r5) d05px, d06n,
d06p, (d06p * r6) d06px, d07n, d07p, (d07p * r7) d07px, d08n,
d08p, (d08p * r8) d08px, d09n, d09p, (d09p * r9) d09px, d10n,
d10p, (d10p * r10) d10px, d11n, d11p, (d11p * r11) d11px,
d12n, d12p, (d12p * r12) d12px, dsumn, dsump
FROM (SELECT 'ds01' plant, cust, (SELECT aza17
FROM ds01.aza_file) curr,
SUM (DECODE (cdate, '201101', x1, 0)) d01n,
SUM (DECODE (cdate, '201101', x2, 0)) d01p,
SUM (DECODE (cdate, '201102', x1, 0)) d02n,
SUM (DECODE (cdate, '201102', x2, 0)) d02p,
SUM (DECODE (cdate, '201103', x1, 0)) d03n,
SUM (DECODE (cdate, '201103', x2, 0)) d03p,
SUM (DECODE (cdate, '201104', x1, 0)) d04n,
SUM (DECODE (cdate, '201104', x2, 0)) d04p,
SUM (DECODE (cdate, '201105', x1, 0)) d05n,
SUM (DECODE (cdate, '201105', x2, 0)) d05p,
SUM (DECODE (cdate, '201106', x1, 0)) d06n,
SUM (DECODE (cdate, '201106', x2, 0)) d06p,
SUM (DECODE (cdate, '201107', x1, 0)) d07n,
SUM (DECODE (cdate, '201107', x2, 0)) d07p,
SUM (DECODE (cdate, '201108', x1, 0)) d08n,
SUM (DECODE (cdate, '201108', x2, 0)) d08p,
SUM (DECODE (cdate, '201109', x1, 0)) d09n,
SUM (DECODE (cdate, '201109', x2, 0)) d09p,
SUM (DECODE (cdate, '201110', x1, 0)) d10n,
SUM (DECODE (cdate, '201110', x2, 0)) d10p,
SUM (DECODE (cdate, '201111', x1, 0)) d11n,
SUM (DECODE (cdate, '201111', x2, 0)) d11p,
SUM (DECODE (cdate, '201112', x1, 0)) d12n,
SUM (DECODE (cdate, '201112', x2, 0)) d12p,
SUM (x1) dsumn, SUM (x2) dsump
FROM (SELECT occ02 AS cust,
TO_CHAR (oga02, 'yyyymm') AS cdate,
SUM (ogb12) x1,
SUM (ogb12 * ogb13 * oga24) x2,
ROW_NUMBER () OVER (PARTITION BY occ02, TO_CHAR
(oga02,
'yyyymm'
) ORDER BY occ02)
rn
FROM ds01.ima_file,
ds01.oga_file,
ds01.ogb_file,
ds01.occ_file
WHERE oga01 = ogb01
AND ogb04 = ima01
AND oga09 <> '1'
AND oga09 <> '5'
AND ogaconf = 'Y'
AND ogapost = 'Y'
AND SUBSTR (oga03, 1, 1) <> 'E'
AND SUBSTR (oga18, 1, 1) <> 'E'
AND oga18 = occ01
AND TO_CHAR (oga02, 'yyyymm') <= '201112'
AND TO_CHAR (oga02, 'yyyymm') >= '201101'
GROUP BY occ02, TO_CHAR (oga02, 'yyyymm'))
GROUP BY cust
) t1,
(SELECT axp03 curr, SUM (DECODE (axp02, 1, rate, 0)) r1,
SUM (DECODE (axp02, 2, rate, 0)) r2,
SUM (DECODE (axp02, 3, rate, 0)) r3,
SUM (DECODE (axp02, 4, rate, 0)) r4,
SUM (DECODE (axp02, 5, rate, 0)) r5,
SUM (DECODE (axp02, 6, rate, 0)) r6,
SUM (DECODE (axp02, 7, rate, 0)) r7,
SUM (DECODE (axp02, 8, rate, 0)) r8,
SUM (DECODE (axp02, 9, rate, 0)) r9,
SUM (DECODE (axp02, 10, rate, 0)) r10,
SUM (DECODE (axp02, 11, rate, 0)) r11,
SUM (DECODE (axp02, 12, rate, 0)) r12
FROM (SELECT axp03, axp01, axp02, axp07 AS rate
FROM ds00.axp_file
WHERE axp01 = 2011)
GROUP BY axp03) t2
WHERE t1.curr = t2.curr(+))
GROUP BY GROUPING SETS ((plant), (plant, cust), ())
全站熱搜
留言列表
站台介紹 (1)