Good God.... It works.
SELECT
MIN(S.CREATEDATE) CREATEDATE,
MAX(S.MODIFYDATE) MODIFYDATE,
S.ACCOUNTID, A.ACCOUNT,
U.USERTEXT1 AREAID, U.USERTEXT3 AREATYPE,
(CASE WHEN UPPER(U.USERTEXT3) = 'REGION' THEN U.USERTEXT1 END) Region,
(CASE WHEN UPPER(U.USERTEXT3) = 'DISTRICT' THEN U.USERTEXT1 END) District,
(CASE WHEN UPPER(U.USERTEXT3) = 'TERRITORY' THEN U.USERTEXT1 END) Territory,
S.PRODUCT,
S.CATALOG_NUM,
S.DISTRIBUTOR,
S.YR,
SUM( CASE WHEN S.MO = 1 THEN S.QUANTITY END) JAN_QTY,
SUM( CASE WHEN S.MO = 2 THEN S.QUANTITY END) FEB_QTY,
SUM( CASE WHEN S.MO = 3 THEN S.QUANTITY END) MAR_QTY,
SUM( CASE WHEN S.MO = 4 THEN S.QUANTITY END) APR_QTY,
SUM( CASE WHEN S.MO = 5 THEN S.QUANTITY END) MAY_QTY,
SUM( CASE WHEN S.MO = 6 THEN S.QUANTITY END) JUN_QTY,
SUM( CASE WHEN S.MO = 7 THEN S.QUANTITY END) JUL_QTY,
SUM( CASE WHEN S.MO = 8 THEN S.QUANTITY END) AUG_QTY,
SUM( CASE WHEN S.MO = 9 THEN S.QUANTITY END) SEP_QTY,
SUM( CASE WHEN S.MO = 10 THEN S.QUANTITY END) OCT_QTY,
SUM( CASE WHEN S.MO = 11 THEN S.QUANTITY END) NOV_QTY,
SUM( CASE WHEN S.MO = 12 THEN S.QUANTITY END) DEC_QTY,
SUM(S.QUANTITY) YR_QTY,
SUM( CASE WHEN S.MO = 1 THEN S.SALES_AMOUNT END) JAN_AMT,
SUM( CASE WHEN S.MO = 2 THEN S.SALES_AMOUNT END) FEB_AMT,
SUM( CASE WHEN S.MO = 3 THEN S.SALES_AMOUNT END) MAR_AMT,
SUM( CASE WHEN S.MO = 4 THEN S.SALES_AMOUNT END) APR_AMT,
SUM( CASE WHEN S.MO = 5 THEN S.SALES_AMOUNT END) MAY_AMT,
SUM( CASE WHEN S.MO = 6 THEN S.SALES_AMOUNT END) JUN_AMT,
SUM( CASE WHEN S.MO = 7 THEN S.SALES_AMOUNT END) JUL_AMT,
SUM( CASE WHEN S.MO = 8 THEN S.SALES_AMOUNT END) AUG_AMT,
SUM( CASE WHEN S.MO = 9 THEN S.SALES_AMOUNT END) SEP_AMT,
SUM( CASE WHEN S.MO = 10 THEN S.SALES_AMOUNT END) OCT_AMT,
SUM( CASE WHEN S.MO = 11 THEN S.SALES_AMOUNT END) NOV_AMT,
SUM( CASE WHEN S.MO = 12 THEN S.SALES_AMOUNT END) DEC_AMT,
SUM(S.SALES_AMOUNT) YR_AMT
FROM C_ACC_SALES S INNER JOIN ACCOUNT A ON S.ACCOUNTID = A.ACCOUNTID
INNER JOIN USERPROFILE U ON A.ACCOUNTMANAGERID = U.USERID
WHERE S.YR = EXTRACT(YEAR FROM SYSDATE) OR S.YR = EXTRACT(YEAR FROM
ADD_MONTHS(SYSDATE, -12))
GROUP BY S.ACCOUNTID, S.YR, A.ACCOUNT, U.USERTEXT1, U.USERTEXT3, Territory,
S.PRODUCT, S.CATALOG_NUM, S.DISTRIBUTOR
"Tony Andrews" <andrewst_at_onetel.com> wrote in message
news:1117547851.306756.154510_at_z14g2000cwz.googlegroups.com...
> Jon Davis wrote:
>> This might be too much hassle for most of you but someone might be up for
>> a
>> good challenge. I need to figure out how to optimize this query (at
>> bottom
>> of this post) for Oracle 9i.
>
> I can't possibly work on that enormous query, but have you considered
> using this common approach to "pivot" queries instead?
>
> SELECT A.ACCOUNTID, ...
> SUM( CASE WHEN A.MO = 1 THEN A.QUANTITY END) JAN_QTY,
> SUM( CASE WHEN A.MO = 2 THEN A.QUANTITY END) FEB_QTY,
> ...
> SUM(A.QUANTITY) YR_QTY
> FROM C_ACC_SALES A
> WHERE ...
> GROUP BY A.ACCOUNTID, ...;
>
Received on Tue May 31 2005 - 09:48:54 CDT
