У меня вот такой вариант завалялся, похоже это вообще из SpotLight-а выдернули. Я такого точно не писала.
Код:
SELECT /*+ ORDERED USE_NL(S T) FULL(G) INDEX(S) INDEX(T) */
G.LOCID AS locid ,
G.ARTICLE AS article ,
SUM(S.QUANTITY) AS qty ,
ROUND(SUM(S.TOTALPRICE) /
DECODE(SUM(S.QUANTITY), 0, NULL, SUM(S.QUANTITY)),
1) AS price1 ,
ROUND((SUM(S.TOTALPRICE) - SUM(NVL(T.TAXSUM, 0))) /
DECODE(SUM(S.QUANTITY), 0, NULL, SUM(S.QUANTITY)),
1) AS price2
FROM
(SELECT T.ID AS locid ,
T.ARTICLE AS article ,
SUBSTR(T.MAX_DATA, 9) AS id ,
TO_DATE(SUBSTR(T.MAX_DATA, 1, 8), 'YYYYMMDD') AS createdat
FROM (SELECT D.LOCATIONTO ID,
S.ARTICLE,
MAX(TO_CHAR(D.CREATEDAT, 'YYYYMMDD') || D.ID) MAX_DATA
FROM --SUPERMAG.TTLOCPARENTLIST L,
SUPERMAG.SMDOCUMENTS D,
SUPERMAG.SMSPEC S
WHERE D.DOCTYPE = S.DOCTYPE
AND D.ID = S.DOCID
AND L.PARENTID = D.LOCATIONTO
AND D.DOCSTATE >= 3
AND D.OPCODE = 0
AND D.DOCTYPE = 'WI'
AND D.CREATEDAT <= TO_DATE('20.12.2006', 'DD.MM.YYYY')
GROUP BY D.LOCATIONTO, S.ARTICLE) T
WHERE 1 = 1
) G, SUPERMAG.SMSPEC S, SUPERMAG.SMSPECTAX T
WHERE 'WI' = S.DOCTYPE
AND G.ID = S.DOCID
AND G.ARTICLE = S.ARTICLE
AND S.DOCTYPE = T.DOCTYPE(+)
AND S.DOCID = T.DOCID(+)
AND S.SPECITEM = T.SPECITEM(+)
GROUP BY G.LOCID, G.ARTICLE