search

JSQLParser / JSqlParser

JSqlParser parses an SQL statement and translate it into a hierarchy of Java classes. The generated hierarchy can be navigated using the Visitor Pattern
https://github.com/JSQLParser/JSqlParser/wiki
Apache License 2.0
5.43k stars 1.35k forks source link

Oracle Partition by Parse Error #1623

Closed leedonet closed 2 years ago

leedonet commented 2 years ago

SELECT FROM SUB_DATA_CUST1 A LEFT JOIN SELECT FROM SUB_DATA_CUST2 A WHERE A.STD_YM BETWEEN TO_CHAR (ADD_MONTHS (SYSDATE,-15), 'YYYYMM') AND TO_CHAR (SYSDATE,'yyyymm')
) B PARTITION BY ( B.COMP_CD, B.CUST_CD, B.STD_YM, B.BRND_CD, B.LARG_CTG_CD ) ON A.STD_YM = B.STD_YM GROUP BY B.COMP_CD, B.CUST_CD, B.BRND_CD, B.LARG_CTG_CD

======================

net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "PARTITION" "PARTITION" at line 8, column 2.

Was expecting one of:

";"
"CONNECT"
"EMIT"
"GROUP"
"HAVING"
"PIVOT"
"START"
"WINDOW"
<EOF>

at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:48879)
at net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:48629)
at net.sf.jsqlparser.parser.CCJSqlParser.Statement(CCJSqlParser.java:181)
manticore-projects commented 2 years ago

Greetings.

First of all, your sample has inconsistent brackets:

SELECT *
FROM sub_data_cust1 a
    LEFT JOIN ( SELECT *
                FROM sub_data_cust2 a
                WHERE A.STD_YM BETWEEN TO_CHAR(ADD_MONTHS(SYSDATE, -15), 'YYYYMM') AND TO_CHAR(SYSDATE, 'yyyymm') ) b
-- PARTITION BY ( B.COMP_CD, B.CUST_CD, B.STD_YM, B.BRND_CD, B.LARG_CTG_CD )
        ON a.std_ym = b.std_ym
GROUP BY    b.comp_cd
            , b.cust_cd
            , b.brnd_cd
            , b.larg_ctg_cd
;

Secondly, I am not sure if you use PARTITION BY correctly, are you sure Oracle accepts it exactly this way?

manticore-projects commented 2 years ago

This is a valid example and JSQLParser parses it:

SELECT id, name, gender,
COUNT(gender) OVER (PARTITION BY gender) AS Total_students,
AVG(age) OVER (PARTITION BY gender) AS Average_Age,
SUM(total_score) OVER (PARTITION BY gender) AS Total_Score
FROM student
wumpz commented 2 years ago

To my knowledge partition by is only used in window functions or like Oracle calls it: analytic funtions. Your useage is not supported. If I am wrong, point us to the syntax definition of your usage of partition by.

leedonet commented 2 years ago 
SELECT
    *  
FROM
    (WITH SUB_DATA_CUST  AS ( SELECT
        /*+USE_HASH(A C)*/  'C'  AS COMP_CD,  A.STD_YM,  A.BRND_CD,  A.LARG_CTG_CD,  A.CUST_CD,  SUM (A.NET_SALE_AMT) AS SALE_AMT,  SUM (A.NET_SALE_QTY) AS SALE_QTY,  SUM (A.SALE_PROF_AMT) AS PROF_AMT,  SUM (ABS (A.SALE_RTN_AMT)) AS RTN_AMT  
    FROM
        T_EIS_SALE_CUST_EMP C  
    INNER JOIN
        T_EIS_CUST_BRND_SALE_P A  
            ON C.CUST_CD = A.CUST_CD  
            AND C.CHAR_EMP_CLS = A.CHAR_EMP_CLS  
    WHERE
        0 = 1 
        AND ( A.STD_YM BETWEEN TO_CHAR (  ADD_MONTHS (SYSDATE, -8),  'YYYYMM')  AND TO_CHAR (SYSDATE, 'yyyymm')  
        OR A.STD_YM BETWEEN CASE  
            WHEN SUBSTR (  TO_CHAR (  SYSDATE,  'yyyymm'),  5,  6) IN  ('02', '03')  THEN  TO_CHAR (  ADD_MONTHS (  TO_DATE (  TO_CHAR (  SYSDATE,  'yyyymm'),  'YYYYMM'),  -15),  'YYYYMM')  
            ELSE  TO_CHAR (  ADD_MONTHS (  TO_DATE (  TO_CHAR (  SYSDATE,  'yyyymm'),  'YYYYMM'),  -13),  'YYYY')  || '01'  
        END  AND TO_CHAR (SYSDATE, 'yyyymm')    
        OR A.STD_YM BETWEEN TO_CHAR (  ADD_MONTHS (SYSDATE, -15),  'YYYYMM')  AND TO_CHAR (SYSDATE, 'yyyymm'))  
        AND C.GENE_SPEC_CLS = 'G'   
        AND C.EMP_NO = '1020011'  
        AND C.CUST_CD IN ('31320', '18320')  
    GROUP BY
        A.STD_YM,  A.BRND_CD,  A.LARG_CTG_CD,  A.CUST_CD  
    HAVING
        SUM (A.NET_SALE_AMT) <> 0  
        OR SUM (A.NET_SALE_QTY) <> 0  
        OR SUM (A.SALE_PROF_AMT) <> 0  
        OR SUM (ABS (A.SALE_RTN_AMT)) <> 0),
    SUB_DATA  AS ( SELECT
        A.COMP_CD,  A.STD_YM,  A.BRND_CD,  A.LARG_CTG_CD,  A.CUST_CD,  SUM (SALE_AMT) AS SALE_AMT,  SUM (SALE_QTY) AS SALE_QTY,  SUM (PROF_AMT) AS PROF_AMT,  SUM (RTN_AMT) AS RTN_AMT  
    FROM
        SUB_DATA_CUST A  
    GROUP BY
        A.COMP_CD,  A.STD_YM,  A.BRND_CD,  A.LARG_CTG_CD,  A.CUST_CD),
    SUB_DATA_SCORE  AS (  SELECT
        A.COMP_CD,  A.CUST_CD,  A.BRND_CD,  A.LARG_CTG_CD,  CASE  
            WHEN SALE_AMT_CUR_YEAR_ACC_AVG > 0  
            AND SALE_AMT_PREV_YEAR_ACC_AVG <= 0  THEN  100  
            WHEN SALE_AMT_CUR_YEAR_ACC_AVG <= 0  THEN  0  
            ELSE  TRUNC (  SALE_AMT_CUR_YEAR_ACC_AVG  / SALE_AMT_PREV_YEAR_ACC_AVG  * 100)  
        END  AS B_SCORE ,  CASE  
            WHEN SALE_AMT_PREV_3 > 0 
            AND SALE_AMT_PREV_6 <= 0  THEN  100  
            WHEN SALE_AMT_PREV_3 <= 0  THEN  0  
            ELSE  TRUNC (SALE_AMT_PREV_3 / SALE_AMT_PREV_6 * 100)  
        END  AS A_SCORE  
    FROM
        ( SELECT
            A.COMP_CD,  A.CUST_CD,  A.BRND_CD,  A.LARG_CTG_CD  ,  SUM (  CASE  
                WHEN A.STD_YM BETWEEN TO_CHAR (  ADD_MONTHS (  TO_DATE (  TO_CHAR (  SYSDATE,  'yyyymm'),  'YYYYMM'),  -3),  'YYYYMM')  AND TO_CHAR (  ADD_MONTHS (  TO_DATE (  TO_CHAR (  SYSDATE,  'yyyymm'),  'YYYYMM'),  -1),  'YYYYMM')  THEN  A.SALE_AMT  
                ELSE  0  
            END)  / 3  AS SALE_AMT_PREV_3  ,  SUM (  CASE  
                WHEN A.STD_YM BETWEEN TO_CHAR (  ADD_MONTHS (  TO_DATE (  TO_CHAR (  SYSDATE,  'yyyymm'),  'YYYYMM'),  -9),  'YYYYMM')  AND TO_CHAR (  ADD_MONTHS (  TO_DATE (  TO_CHAR (  SYSDATE,  'yyyymm'),  'YYYYMM'),  -4),  'YYYYMM')  THEN  A.SALE_AMT  
                ELSE  0  
            END)  / 6  AS SALE_AMT_PREV_6  ,  CASE  
                WHEN SUBSTR (TO_CHAR (SYSDATE, 'yyyymm'),  5,  6) IN  ('02', '03')  THEN  SUM (  CASE  
                    WHEN A.STD_YM BETWEEN TO_CHAR (  ADD_MONTHS (  TO_DATE (  TO_CHAR (  SYSDATE,  'yyyymm'),  'YYYYMM'),  -3),  'YYYYMM')  AND TO_CHAR (  ADD_MONTHS (  TO_DATE (  TO_CHAR (  SYSDATE,  'yyyymm'),  'YYYYMM'),  -1),  'YYYYMM')  THEN  A.SALE_AMT  
                    ELSE  0  
                END)  / 3  
                ELSE  SUM (  CASE  
                    WHEN A.STD_YM BETWEEN TO_CHAR (  ADD_MONTHS (  TO_DATE (  TO_CHAR (  SYSDATE,  'yyyymm'),  'YYYYMM'),  -1),  'YYYY')  || '01'  AND TO_CHAR (  ADD_MONTHS (  TO_DATE (  TO_CHAR (  SYSDATE,  'yyyymm'),  'YYYYMM'),  -1),  'YYYYMM')  THEN  A.SALE_AMT  
                    ELSE  0  
                END)  / ( MONTHS_BETWEEN (  ADD_MONTHS (  TO_DATE (  TO_CHAR (SYSDATE,  'yyyymm'),  'YYYYMM'),  -1),  TO_DATE (  TO_CHAR (  ADD_MONTHS (  TO_DATE (  TO_CHAR (  SYSDATE,  'yyyymm'),  'YYYYMM'),  -1),  'YYYY')  || '01',  'YYYYMM'))  + 1)  
            END  AS SALE_AMT_CUR_YEAR_ACC_AVG,  CASE  
                WHEN SUBSTR (TO_CHAR (SYSDATE, 'yyyymm'),  5,  6) IN  ('02', '03')  THEN  SUM (  CASE  
                    WHEN A.STD_YM BETWEEN TO_CHAR (  ADD_MONTHS (  TO_DATE (  TO_CHAR (  SYSDATE,  'yyyymm'),  'YYYYMM'),  -15),  'YYYYMM')  AND TO_CHAR (  ADD_MONTHS (  TO_DATE (  TO_CHAR (  SYSDATE,  'yyyymm'),  'YYYYMM'),  -13),  'YYYYMM')  THEN  A.SALE_AMT  
                    ELSE  0  
                END)  / 3  
                ELSE  SUM (  CASE  
                    WHEN A.STD_YM BETWEEN TO_CHAR (  ADD_MONTHS (  TO_DATE (  TO_CHAR (  SYSDATE,  'yyyymm'),  'YYYYMM'),  -13),  'YYYY')  || '01'  AND TO_CHAR (  ADD_MONTHS (  TO_DATE (  TO_CHAR (  SYSDATE,  'yyyymm'),  'YYYYMM'),  -13),  'YYYYMM')  THEN  A.SALE_AMT  
                    ELSE  0  
                END)  / ( MONTHS_BETWEEN (  ADD_MONTHS (  TO_DATE (  TO_CHAR (SYSDATE,  'yyyymm'),  'YYYYMM'),  -13),  TO_DATE (  TO_CHAR (  ADD_MONTHS (  TO_DATE (  TO_CHAR (  SYSDATE,  'yyyymm'),  'YYYYMM'),  -13),  'YYYY')  || '01',  'YYYYMM'))  + 1)  
            END  AS SALE_AMT_PREV_YEAR_ACC_AVG  
        FROM
            SUB_DATA A  
        GROUP BY
            A.COMP_CD,  A.CUST_CD,  A.BRND_CD,  A.LARG_CTG_CD) A)  SELECT
            A.COMP_CD,
            A.CUST_CD,
            'G'  AS GENE_SPEC_CLS,
            ROWNUM AS ROW_NUM,
            A.BRND_CD,
            (SELECT
                BRND_NM  
            FROM
                T_SBI_BRAND  
            WHERE
                0 = 1 
                AND BRND_CD = A.BRND_CD)  AS BRND_NM,
            A.LARG_CTG_CD,
            (SELECT
                FN_GET_DEPT_NM (MAX (CHAR_DEPT_CD))  
            FROM
                T_EIS_D_LARG_CTG  
            WHERE
                0 = 1 
                AND USE_YN = 'Y'  
                AND COMP_CD = 'C'  
                AND LARG_CTG_CD = A.LARG_CTG_CD)  AS CHAR_DEPT_NM,
            (SELECT
                ITEM_LARG_CTG_NM  
            FROM
                T_SBI_ITEM_LCTG  
            WHERE
                0 = 1 
                AND ITEM_LARG_CTG_CD = A.LARG_CTG_CD)  AS LARG_CTG_NM    ,
            ROUND (  CASE  
                WHEN A.TOT_AMT = 0 THEN 0  
                ELSE A.PROF_AMT / A.TOT_AMT * 100  
            END,  1)  AS AVG_PROF  ,
            CASE  
                WHEN MON_CNT = 0  THEN  0  
                ELSE  ROUND (  CASE  
                    WHEN MON_CNT >= 8  THEN  (A.TOT_AMT - A.MIN_AMT - A.MAX_AMT) / 6  
                    WHEN MON_CNT = 7  THEN  (A.TOT_AMT - A.MIN_AMT - A.MAX_AMT) / 5  
                    WHEN MON_CNT <= 6  THEN  A.TOT_AMT / MON_CNT  
                END)  
            END  AS AVG_AMT,
            CASE  
                WHEN MON_CNT = 0  THEN  0  
                ELSE  ROUND (  CASE  
                    WHEN MON_CNT >= 8  THEN  (A.TOT_QTY - A.MIN_QTY - A.MAX_QTY) / 6  
                    WHEN MON_CNT = 7  THEN  (A.TOT_QTY - A.MIN_QTY - A.MAX_QTY) / 5  
                    WHEN MON_CNT <= 6  THEN  A.TOT_QTY / MON_CNT  
                END)  
            END  AS AVG_QTY,
            CASE  
                WHEN MON_CNT = 0  THEN  0  
                ELSE  ROUND (  CASE  
                    WHEN MON_CNT >= 8  THEN  ( A.TOT_RTN_AMT  - A.MIN_RTN_AMT  - A.MAX_RTN_AMT)  / 6  
                    WHEN MON_CNT = 7  THEN  ( A.TOT_RTN_AMT  - A.MIN_RTN_AMT  - A.MAX_RTN_AMT)  / 5  
                    WHEN MON_CNT <= 6  THEN  A.TOT_RTN_AMT / MON_CNT  
                END)  
            END  AS AVG_RTN_AMT,
            ROUND (  CASE  
                WHEN A.TOT_AMT = 0 THEN 0  
                ELSE A.TOT_RTN_AMT / A.TOT_AMT * 100  
            END,  1)  AS AVG_RTN_RTE  ,
            A.SALE_AMT,
            A.MAX_AMT,
            A.MIN_AMT  ,
            A.HS_SALE_AMT_1,
            A.HS_SALE_AMT_2,
            A.HS_SALE_AMT_3,
            A.HS_SALE_AMT_4,
            A.HS_SALE_AMT_5,
            A.HS_SALE_AMT_6,
            A.HS_SALE_AMT_7,
            A.HS_SALE_AMT_8,
            A.HS_SALE_AMT_9,
            A.HS_SALE_AMT_10,
            A.HS_SALE_AMT_11,
            A.HS_SALE_AMT_12,
            A.HS_SALE_AMT_13,
            A.HS_SALE_AMT_14,
            A.HS_SALE_AMT_15,
            A.HS_SALE_AMT_16,
            A.HS_RTN_AMT_1,
            A.HS_RTN_AMT_2,
            A.HS_RTN_AMT_3,
            A.HS_RTN_AMT_4,
            A.HS_RTN_AMT_5,
            A.HS_RTN_AMT_6,
            A.HS_RTN_AMT_7,
            A.HS_RTN_AMT_8,
            A.HS_RTN_AMT_9,
            A.HS_RTN_AMT_10,
            A.HS_RTN_AMT_11,
            A.HS_RTN_AMT_12,
            A.HS_RTN_AMT_13,
            A.HS_SALE_QTY_1,
            A.HS_SALE_QTY_2,
            A.HS_SALE_QTY_3,
            A.HS_SALE_QTY_4,
            A.HS_SALE_QTY_5,
            A.HS_SALE_QTY_6,
            A.HS_SALE_QTY_7,
            A.HS_SALE_QTY_8,
            A.HS_SALE_QTY_9,
            A.HS_SALE_QTY_10,
            A.HS_SALE_QTY_11,
            A.HS_SALE_QTY_12,
            A.HS_SALE_QTY_13,
            A.TOT_CUST_CNT,
            A.HS_CUST_CNT_1,
            A.HS_CUST_CNT_2,
            A.HS_CUST_CNT_3,
            A.HS_CUST_CNT_4,
            A.HS_CUST_CNT_5,
            A.HS_CUST_CNT_6,
            A.HS_CUST_CNT_7,
            A.HS_CUST_CNT_8,
            A.HS_CUST_CNT_9,
            A.HS_CUST_CNT_10,
            A.HS_CUST_CNT_11,
            A.HS_CUST_CNT_12,
            A.HS_CUST_CNT_13,
            ROUND (  CASE  
                WHEN A.HS_SALE_AMT_1 = 0 THEN 0  
                ELSE A.HS_PROF_AMT_1 / A.HS_SALE_AMT_1 * 100  
            END,  1)  AS HS_PROF_RTE_1,
            ROUND (  CASE  
                WHEN A.HS_SALE_AMT_2 = 0 THEN 0  
                ELSE A.HS_PROF_AMT_2 / A.HS_SALE_AMT_2 * 100  
            END,  1)  AS HS_PROF_RTE_2,
            ROUND (  CASE  
                WHEN A.HS_SALE_AMT_3 = 0 THEN 0  
                ELSE A.HS_PROF_AMT_3 / A.HS_SALE_AMT_3 * 100  
            END,  1)  AS HS_PROF_RTE_3,
            ROUND (  CASE  
                WHEN A.HS_SALE_AMT_4 = 0 THEN 0  
                ELSE A.HS_PROF_AMT_4 / A.HS_SALE_AMT_4 * 100  
            END,  1)  AS HS_PROF_RTE_4,
            ROUND (  CASE  
                WHEN A.HS_SALE_AMT_5 = 0 THEN 0  
                ELSE A.HS_PROF_AMT_5 / A.HS_SALE_AMT_5 * 100  
            END,  1)  AS HS_PROF_RTE_5,
            ROUND (  CASE  
                WHEN A.HS_SALE_AMT_6 = 0 THEN 0  
                ELSE A.HS_PROF_AMT_6 / A.HS_SALE_AMT_6 * 100  
            END,  1)  AS HS_PROF_RTE_6,
            ROUND (  CASE  
                WHEN A.HS_SALE_AMT_7 = 0 THEN 0  
                ELSE A.HS_PROF_AMT_7 / A.HS_SALE_AMT_7 * 100  
            END,  1)  AS HS_PROF_RTE_7,
            ROUND (  CASE  
                WHEN A.HS_SALE_AMT_8 = 0 THEN 0  
                ELSE A.HS_PROF_AMT_8 / A.HS_SALE_AMT_8 * 100  
            END,  1)  AS HS_PROF_RTE_8,
            ROUND (  CASE  
                WHEN A.HS_SALE_AMT_9 = 0 THEN 0  
                ELSE A.HS_PROF_AMT_9 / A.HS_SALE_AMT_9 * 100  
            END,  1)  AS HS_PROF_RTE_9,
            ROUND (  CASE  
                WHEN A.HS_SALE_AMT_10 = 0 THEN 0  
                ELSE A.HS_PROF_AMT_10 / A.HS_SALE_AMT_10 * 100  
            END,  1)  AS HS_PROF_RTE_10,
            ROUND (  CASE  
                WHEN A.HS_SALE_AMT_11 = 0 THEN 0  
                ELSE A.HS_PROF_AMT_11 / A.HS_SALE_AMT_11 * 100  
            END,  1)  AS HS_PROF_RTE_11,
            ROUND (  CASE  
                WHEN A.HS_SALE_AMT_12 = 0 THEN 0  
                ELSE A.HS_PROF_AMT_12 / A.HS_SALE_AMT_12 * 100  
            END,  1)  AS HS_PROF_RTE_12,
            ROUND (  CASE  
                WHEN A.HS_SALE_AMT_13 = 0 THEN 0  
                ELSE A.HS_PROF_AMT_13 / A.HS_SALE_AMT_13 * 100  
            END,  1)  AS HS_PROF_RTE_13,
            ROUND (  CASE  
                WHEN A.HS_SALE_AMT_1 = 0 THEN 0  
                ELSE A.HS_RTN_AMT_1 / A.HS_SALE_AMT_1 * 100  
            END,  1)  AS HS_RTN_RTE_1,
            ROUND (  CASE  
                WHEN A.HS_SALE_AMT_2 = 0 THEN 0  
                ELSE A.HS_RTN_AMT_2 / A.HS_SALE_AMT_2 * 100  
            END,  1)  AS HS_RTN_RTE_2,
            ROUND (  CASE  
                WHEN A.HS_SALE_AMT_3 = 0 THEN 0  
                ELSE A.HS_RTN_AMT_3 / A.HS_SALE_AMT_3 * 100  
            END,  1)  AS HS_RTN_RTE_3,
            ROUND (  CASE  
                WHEN A.HS_SALE_AMT_4 = 0 THEN 0  
                ELSE A.HS_RTN_AMT_4 / A.HS_SALE_AMT_4 * 100  
            END,  1)  AS HS_RTN_RTE_4,
            ROUND (  CASE  
                WHEN A.HS_SALE_AMT_5 = 0 THEN 0  
                ELSE A.HS_RTN_AMT_5 / A.HS_SALE_AMT_5 * 100  
            END,  1)  AS HS_RTN_RTE_5,
            ROUND (  CASE  
                WHEN A.HS_SALE_AMT_6 = 0 THEN 0  
                ELSE A.HS_RTN_AMT_6 / A.HS_SALE_AMT_6 * 100  
            END,  1)  AS HS_RTN_RTE_6,
            ROUND (  CASE  
                WHEN A.HS_SALE_AMT_7 = 0 THEN 0  
                ELSE A.HS_RTN_AMT_7 / A.HS_SALE_AMT_7 * 100  
            END,  1)  AS HS_RTN_RTE_7,
            ROUND (  CASE  
                WHEN A.HS_SALE_AMT_8 = 0 THEN 0  
                ELSE A.HS_RTN_AMT_8 / A.HS_SALE_AMT_8 * 100  
            END,  1)  AS HS_RTN_RTE_8,
            ROUND (  CASE  
                WHEN A.HS_SALE_AMT_9 = 0 THEN 0  
                ELSE A.HS_RTN_AMT_9 / A.HS_SALE_AMT_9 * 100  
            END,  1)  AS HS_RTN_RTE_9,
            ROUND (  CASE  
                WHEN A.HS_SALE_AMT_10 = 0 THEN 0  
                ELSE A.HS_RTN_AMT_10 / A.HS_SALE_AMT_10 * 100  
            END,  1)  AS HS_RTN_RTE_10,
            ROUND (  CASE  
                WHEN A.HS_SALE_AMT_11 = 0 THEN 0  
                ELSE A.HS_RTN_AMT_11 / A.HS_SALE_AMT_11 * 100  
            END,  1)  AS HS_RTN_RTE_11,
            ROUND (  CASE  
                WHEN A.HS_SALE_AMT_12 = 0 THEN 0  
                ELSE A.HS_RTN_AMT_12 / A.HS_SALE_AMT_12 * 100  
            END,  1)  AS HS_RTN_RTE_12,
            ROUND (  CASE  
                WHEN A.HS_SALE_AMT_13 = 0 THEN 0  
                ELSE A.HS_RTN_AMT_13 / A.HS_SALE_AMT_13 * 100  
            END,  1)  AS HS_RTN_RTE_13  ,
            SCORE.A_SCORE,
            SCORE.B_SCORE,
            TRUNC ( (SCORE.A_SCORE * 0.45) + (SCORE.B_SCORE * 0.55))  AS TOTAL_SCORE,
            CASE  
                WHEN NVL (CTX_RECM.RECM_ITEM_CNT, 0) > 0 THEN 'Y'  
                ELSE ''  
            END  CTX_RECM_YN  
        FROM
            ( SELECT
                COMP_CD,
                CUST_CD,
                BRND_CD,
                LARG_CTG_CD  ,
                SUM (TOT_AMT) AS TOT_AMT,
                SUM (MAX_AMT) AS MAX_AMT,
                SUM (MIN_AMT) AS MIN_AMT,
                SUM (SALE_AMT) AS SALE_AMT  ,
                SUM (TOT_RTN_AMT) AS TOT_RTN_AMT,
                SUM (MAX_RTN_AMT) AS MAX_RTN_AMT,
                SUM (MIN_RTN_AMT) AS MIN_RTN_AMT,
                SUM (RTN_AMT) AS RTN_AMT  ,
                SUM (TOT_QTY) AS TOT_QTY,
                SUM (MAX_QTY) AS MAX_QTY,
                SUM (MIN_QTY) AS MIN_QTY,
                SUM (SALE_QTY) AS SALE_QTY  ,
                SUM (PROF_AMT) AS PROF_AMT,
                SUM (MAX_PROF_AMT) AS MAX_PROF_AMT,
                SUM (MAX_PROF_AMT) AS MIN_PROF_AMT  ,
                SUM (HS_SALE_AMT_1) AS HS_SALE_AMT_1,
                SUM (HS_SALE_AMT_2) AS HS_SALE_AMT_2,
                SUM (HS_SALE_AMT_3) AS HS_SALE_AMT_3,
                SUM (HS_SALE_AMT_4) AS HS_SALE_AMT_4,
                SUM (HS_SALE_AMT_5) AS HS_SALE_AMT_5,
                SUM (HS_SALE_AMT_6) AS HS_SALE_AMT_6,
                SUM (HS_SALE_AMT_7) AS HS_SALE_AMT_7,
                SUM (HS_SALE_AMT_8) AS HS_SALE_AMT_8,
                SUM (HS_SALE_AMT_9) AS HS_SALE_AMT_9,
                SUM (HS_SALE_AMT_10) AS HS_SALE_AMT_10,
                SUM (HS_SALE_AMT_11) AS HS_SALE_AMT_11,
                SUM (HS_SALE_AMT_12) AS HS_SALE_AMT_12,
                SUM (HS_SALE_AMT_13) AS HS_SALE_AMT_13,
                SUM (HS_SALE_AMT_14) AS HS_SALE_AMT_14,
                SUM (HS_SALE_AMT_15) AS HS_SALE_AMT_15,
                SUM (HS_SALE_AMT_16) AS HS_SALE_AMT_16  ,
                SUM (HS_RTN_AMT_1) AS HS_RTN_AMT_1,
                SUM (HS_RTN_AMT_2) AS HS_RTN_AMT_2,
                SUM (HS_RTN_AMT_3) AS HS_RTN_AMT_3,
                SUM (HS_RTN_AMT_4) AS HS_RTN_AMT_4,
                SUM (HS_RTN_AMT_5) AS HS_RTN_AMT_5,
                SUM (HS_RTN_AMT_6) AS HS_RTN_AMT_6,
                SUM (HS_RTN_AMT_7) AS HS_RTN_AMT_7,
                SUM (HS_RTN_AMT_8) AS HS_RTN_AMT_8,
                SUM (HS_RTN_AMT_9) AS HS_RTN_AMT_9,
                SUM (HS_RTN_AMT_10) AS HS_RTN_AMT_10,
                SUM (HS_RTN_AMT_11) AS HS_RTN_AMT_11,
                SUM (HS_RTN_AMT_12) AS HS_RTN_AMT_12,
                SUM (HS_RTN_AMT_13) AS HS_RTN_AMT_13  ,
                SUM (HS_SALE_QTY_1) AS HS_SALE_QTY_1,
                SUM (HS_SALE_QTY_2) AS HS_SALE_QTY_2,
                SUM (HS_SALE_QTY_3) AS HS_SALE_QTY_3,
                SUM (HS_SALE_QTY_4) AS HS_SALE_QTY_4,
                SUM (HS_SALE_QTY_5) AS HS_SALE_QTY_5,
                SUM (HS_SALE_QTY_6) AS HS_SALE_QTY_6,
                SUM (HS_SALE_QTY_7) AS HS_SALE_QTY_7,
                SUM (HS_SALE_QTY_8) AS HS_SALE_QTY_8,
                SUM (HS_SALE_QTY_9) AS HS_SALE_QTY_9,
                SUM (HS_SALE_QTY_10) AS HS_SALE_QTY_10,
                SUM (HS_SALE_QTY_11) AS HS_SALE_QTY_11,
                SUM (HS_SALE_QTY_12) AS HS_SALE_QTY_12,
                SUM (HS_SALE_QTY_13) AS HS_SALE_QTY_13  ,
                SUM (HS_PROF_AMT_1) AS HS_PROF_AMT_1,
                SUM (HS_PROF_AMT_2) AS HS_PROF_AMT_2,
                SUM (HS_PROF_AMT_3) AS HS_PROF_AMT_3,
                SUM (HS_PROF_AMT_4) AS HS_PROF_AMT_4,
                SUM (HS_PROF_AMT_5) AS HS_PROF_AMT_5,
                SUM (HS_PROF_AMT_6) AS HS_PROF_AMT_6,
                SUM (HS_PROF_AMT_7) AS HS_PROF_AMT_7,
                SUM (HS_PROF_AMT_8) AS HS_PROF_AMT_8,
                SUM (HS_PROF_AMT_9) AS HS_PROF_AMT_9,
                SUM (HS_PROF_AMT_10) AS HS_PROF_AMT_10,
                SUM (HS_PROF_AMT_11) AS HS_PROF_AMT_11,
                SUM (HS_PROF_AMT_12) AS HS_PROF_AMT_12,
                SUM (HS_PROF_AMT_13) AS HS_PROF_AMT_13  ,
                SUM (TOT_CUST_CNT) AS TOT_CUST_CNT,
                SUM (HS_CUST_CNT_1) AS HS_CUST_CNT_1,
                SUM (HS_CUST_CNT_2) AS HS_CUST_CNT_2,
                SUM (HS_CUST_CNT_3) AS HS_CUST_CNT_3,
                SUM (HS_CUST_CNT_4) AS HS_CUST_CNT_4,
                SUM (HS_CUST_CNT_5) AS HS_CUST_CNT_5,
                SUM (HS_CUST_CNT_6) AS HS_CUST_CNT_6,
                SUM (HS_CUST_CNT_7) AS HS_CUST_CNT_7,
                SUM (HS_CUST_CNT_8) AS HS_CUST_CNT_8,
                SUM (HS_CUST_CNT_9) AS HS_CUST_CNT_9,
                SUM (HS_CUST_CNT_10) AS HS_CUST_CNT_10,
                SUM (HS_CUST_CNT_11) AS HS_CUST_CNT_11,
                SUM (HS_CUST_CNT_12) AS HS_CUST_CNT_12,
                SUM (HS_CUST_CNT_13) AS HS_CUST_CNT_13,
                MAX (MON_CNT) AS MON_CNT  
            FROM
                ( SELECT
                    COMP_CD,
                    CUST_CD,
                    BRND_CD,
                    LARG_CTG_CD  ,
                    SUM (  CASE  
                        WHEN STD_YM BETWEEN TO_CHAR (  ADD_MONTHS (  SYSDATE,  -8),  'YYYYMM')  AND TO_CHAR (  ADD_MONTHS (  SYSDATE,  -1),  'YYYYMM')  THEN  TOT_AMT  
                        ELSE  0  
                    END)  AS TOT_AMT,
                    MAX (  CASE  
                        WHEN STD_YM BETWEEN TO_CHAR (  ADD_MONTHS (  SYSDATE,  -8),  'YYYYMM')  AND TO_CHAR (  ADD_MONTHS (  SYSDATE,  -1),  'YYYYMM')  THEN  TOT_AMT  
                    END)  AS MAX_AMT,
                    MIN (  CASE  
                        WHEN STD_YM BETWEEN TO_CHAR (  ADD_MONTHS (  SYSDATE,  -8),  'YYYYMM')  AND TO_CHAR (  ADD_MONTHS (  SYSDATE,  -1),  'YYYYMM')  THEN  TOT_AMT  
                    END)  AS MIN_AMT,
                    SUM (  CASE  
                        WHEN STD_YM =  TO_CHAR (SYSDATE, 'YYYYMM')  THEN  TOT_AMT  
                        ELSE  0  
                    END)  AS SALE_AMT  ,
                    SUM (  CASE  
                        WHEN STD_YM BETWEEN TO_CHAR (  ADD_MONTHS (  SYSDATE,  -8),  'YYYYMM')  AND TO_CHAR (  ADD_MONTHS (  SYSDATE,  -1),  'YYYYMM')  THEN  TOT_RTN_AMT  
                        ELSE  0  
                    END)  AS TOT_RTN_AMT,
                    MAX (  CASE  
                        WHEN STD_YM BETWEEN TO_CHAR (  ADD_MONTHS (  SYSDATE,  -8),  'YYYYMM')  AND TO_CHAR (  ADD_MONTHS (  SYSDATE,  -1),  'YYYYMM')  THEN  TOT_RTN_AMT  
                    END)  AS MAX_RTN_AMT,
                    MIN (  CASE  
                        WHEN STD_YM BETWEEN TO_CHAR (  ADD_MONTHS (  SYSDATE,  -8),  'YYYYMM')  AND TO_CHAR (  ADD_MONTHS (  SYSDATE,  -1),  'YYYYMM')  THEN  TOT_RTN_AMT  
                    END)  AS MIN_RTN_AMT,
                    SUM (  CASE  
                        WHEN STD_YM =  TO_CHAR (SYSDATE, 'yyyymm')  THEN  TOT_RTN_AMT  
                        ELSE  0  
                    END)  AS RTN_AMT  ,
                    SUM (  CASE  
                        WHEN STD_YM BETWEEN TO_CHAR (  ADD_MONTHS (  SYSDATE,  -8),  'YYYYMM')  AND TO_CHAR (  ADD_MONTHS (  SYSDATE,  -1),  'YYYYMM')  THEN  TOT_QTY  
                        ELSE  0  
                    END)  AS TOT_QTY,
                    MAX (  CASE  
                        WHEN STD_YM BETWEEN TO_CHAR (  ADD_MONTHS (  SYSDATE,  -8),  'YYYYMM')  AND TO_CHAR (  ADD_MONTHS (  SYSDATE,  -1),  'YYYYMM')  THEN  TOT_QTY  
                    END)  AS MAX_QTY,
                    MIN (  CASE  
                        WHEN STD_YM BETWEEN TO_CHAR (  ADD_MONTHS (  SYSDATE,  -8),  'YYYYMM')  AND TO_CHAR (  ADD_MONTHS (  SYSDATE,  -1),  'YYYYMM')  THEN  TOT_QTY  
                    END)  AS MIN_QTY,
                    SUM (  CASE  
                        WHEN STD_YM =  TO_CHAR (SYSDATE, 'YYYYMM')  THEN  TOT_QTY  
                        ELSE  0  
                    END)  AS SALE_QTY  ,
                    SUM (  CASE  
                        WHEN STD_YM BETWEEN TO_CHAR (  ADD_MONTHS (  SYSDATE,  -8),  'YYYYMM')  AND TO_CHAR (  ADD_MONTHS (  SYSDATE,  -1),  'YYYYMM')  THEN  PROF_AMT  
                        ELSE  0  
                    END)  AS PROF_AMT  ,
                    MAX (  CASE  
                        WHEN STD_YM BETWEEN TO_CHAR (  ADD_MONTHS (  SYSDATE,  -8),  'YYYYMM')  AND TO_CHAR (  ADD_MONTHS (  SYSDATE,  -1),  'YYYYMM')  THEN  PROF_AMT  
                        ELSE  0  
                    END)  AS MAX_PROF_AMT,
                    MIN (  CASE  
                        WHEN STD_YM BETWEEN TO_CHAR (  ADD_MONTHS (  SYSDATE,  -8),  'YYYYMM')  AND TO_CHAR (  ADD_MONTHS (  SYSDATE,  -1),  'YYYYMM')  THEN  PROF_AMT  
                        ELSE  0  
                    END)  AS MIN_PROF_AMT,
                    MONTHS_BETWEEN (  TO_DATE (  TO_CHAR (ADD_MONTHS (SYSDATE, -1),  'YYYYMM')  || '01',  'YYYYMMDD'),  TO_DATE (MIN (STD_YM) || '01',  'YYYYMMDD'))  + 1  AS MON_CNT  ,
                    0 AS HS_SALE_AMT_1,
                    0 AS HS_SALE_AMT_2,
                    0 AS HS_SALE_AMT_3,
                    0 AS HS_SALE_AMT_4,
                    0 AS HS_SALE_AMT_5,
                    0 AS HS_SALE_AMT_6,
                    0 AS HS_SALE_AMT_7,
                    0 AS HS_SALE_AMT_8,
                    0 AS HS_SALE_AMT_9,
                    0 AS HS_SALE_AMT_10,
                    0 AS HS_SALE_AMT_11,
                    0 AS HS_SALE_AMT_12,
                    0 AS HS_SALE_AMT_13,
                    0 AS HS_SALE_AMT_14,
                    0 AS HS_SALE_AMT_15,
                    0 AS HS_SALE_AMT_16  ,
                    0 AS HS_RTN_AMT_1,
                    0 AS HS_RTN_AMT_2,
                    0 AS HS_RTN_AMT_3,
                    0 AS HS_RTN_AMT_4,
                    0 AS HS_RTN_AMT_5,
                    0 AS HS_RTN_AMT_6,
                    0 AS HS_RTN_AMT_7,
                    0 AS HS_RTN_AMT_8,
                    0 AS HS_RTN_AMT_9,
                    0 AS HS_RTN_AMT_10,
                    0 AS HS_RTN_AMT_11,
                    0 AS HS_RTN_AMT_12,
                    0 AS HS_RTN_AMT_13  ,
                    0 AS HS_SALE_QTY_1,
                    0 AS HS_SALE_QTY_2,
                    0 AS HS_SALE_QTY_3,
                    0 AS HS_SALE_QTY_4,
                    0 AS HS_SALE_QTY_5,
                    0 AS HS_SALE_QTY_6,
                    0 AS HS_SALE_QTY_7,
                    0 AS HS_SALE_QTY_8,
                    0 AS HS_SALE_QTY_9,
                    0 AS HS_SALE_QTY_10,
                    0 AS HS_SALE_QTY_11,
                    0 AS HS_SALE_QTY_12,
                    0 AS HS_SALE_QTY_13  ,
                    0 AS HS_PROF_AMT_1,
                    0 AS HS_PROF_AMT_2,
                    0 AS HS_PROF_AMT_3,
                    0 AS HS_PROF_AMT_4,
                    0 AS HS_PROF_AMT_5,
                    0 AS HS_PROF_AMT_6,
                    0 AS HS_PROF_AMT_7,
                    0 AS HS_PROF_AMT_8,
                    0 AS HS_PROF_AMT_9,
                    0 AS HS_PROF_AMT_10,
                    0 AS HS_PROF_AMT_11,
                    0 AS HS_PROF_AMT_12,
                    0 AS HS_PROF_AMT_13  ,
                    0 AS TOT_CUST_CNT,
                    0 AS HS_CUST_CNT_1,
                    0 AS HS_CUST_CNT_2,
                    0 AS HS_CUST_CNT_3,
                    0 AS HS_CUST_CNT_4,
                    0 AS HS_CUST_CNT_5,
                    0 AS HS_CUST_CNT_6,
                    0 AS HS_CUST_CNT_7,
                    0 AS HS_CUST_CNT_8,
                    0 AS HS_CUST_CNT_9,
                    0 AS HS_CUST_CNT_10,
                    0 AS HS_CUST_CNT_11,
                    0 AS HS_CUST_CNT_12,
                    0 AS HS_CUST_CNT_13  
                FROM
                    ( SELECT
                        COMP_CD,
                        CUST_CD,
                        BRND_CD,
                        LARG_CTG_CD,
                        STD_YM  ,
                        SUM (  CASE  
                            WHEN STD_YM BETWEEN TO_CHAR (  ADD_MONTHS (  SYSDATE,  -8),  'YYYYMM')  AND TO_CHAR (  ADD_MONTHS (  SYSDATE,  -1),  'YYYYMM')  THEN  SALE_AMT  
                            ELSE  0  
                        END)  AS TOT_AMT  ,
                        SUM (  CASE  
                            WHEN STD_YM BETWEEN TO_CHAR (  ADD_MONTHS (  SYSDATE,  -8),  'YYYYMM')  AND TO_CHAR (  ADD_MONTHS (  SYSDATE,  -1),  'YYYYMM')  THEN  RTN_AMT  
                            ELSE  0  
                        END)  AS TOT_RTN_AMT  ,
                        SUM (  CASE  
                            WHEN STD_YM BETWEEN TO_CHAR (  ADD_MONTHS (  SYSDATE,  -8),  'YYYYMM')  AND TO_CHAR (  ADD_MONTHS (  SYSDATE,  -1),  'YYYYMM')  THEN  SALE_QTY  
                            ELSE  0  
                        END)  AS TOT_QTY  ,
                        SUM (  CASE  
                            WHEN STD_YM BETWEEN TO_CHAR (  ADD_MONTHS (  SYSDATE,  -8),  'YYYYMM')  AND TO_CHAR (  ADD_MONTHS (  SYSDATE,  -1),  'YYYYMM')  THEN  PROF_AMT  
                            ELSE  0  
                        END)  AS PROF_AMT  
                    FROM
                        SUB_DATA A  
                    WHERE
                        0 = 1 
                        AND A.STD_YM BETWEEN TO_CHAR (  ADD_MONTHS (  SYSDATE,  -8),  'YYYYMM')  AND TO_CHAR (SYSDATE,  'YYYYMM')  
                    GROUP BY
                        COMP_CD,
                        CUST_CD,
                        BRND_CD,
                        LARG_CTG_CD,
                        STD_YM)  
                GROUP BY
                    COMP_CD,
                    CUST_CD,
                    BRND_CD,
                    LARG_CTG_CD  
                UNION
                ALL  SELECT
                    B.COMP_CD,
                    B.CUST_CD,
                    B.BRND_CD,
                    B.LARG_CTG_CD  ,
                    0 AS TOT_AMT,
                    0 AS MAX_AMT,
                    0 AS MIN_AMT,
                    0 AS SALE_AMT  ,
                    0 AS TOT_RTN_AMT,
                    0 AS MAX_RTN_AMT,
                    0 AS MIN_RTN_AMT,
                    0 AS RTN_AMT  ,
                    0 AS TOT_QTY,
                    0 AS MAX_QTY,
                    0 AS MIN_QTY,
                    0 AS SALE_QTY  ,
                    0 AS PROF_AMT,
                    0 AS MAX_PROF_AMT,
                    0 AS MIN_PROF_AMT,
                    0 AS MON_CNT  ,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 1 THEN B.SALE_AMT  
                        ELSE 0  
                    END)  AS HS_SALE_AMT_1,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 2 THEN B.SALE_AMT  
                        ELSE 0  
                    END)  AS HS_SALE_AMT_2,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 3 THEN B.SALE_AMT  
                        ELSE 0  
                    END)  AS HS_SALE_AMT_3,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 4 THEN B.SALE_AMT  
                        ELSE 0  
                    END)  AS HS_SALE_AMT_4,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 5 THEN B.SALE_AMT  
                        ELSE 0  
                    END)  AS HS_SALE_AMT_5,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 6 THEN B.SALE_AMT  
                        ELSE 0  
                    END)  AS HS_SALE_AMT_6,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 7 THEN B.SALE_AMT  
                        ELSE 0  
                    END)  AS HS_SALE_AMT_7,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 8 THEN B.SALE_AMT  
                        ELSE 0  
                    END)  AS HS_SALE_AMT_8,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 9 THEN B.SALE_AMT  
                        ELSE 0  
                    END)  AS HS_SALE_AMT_9,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 10 THEN B.SALE_AMT  
                        ELSE 0  
                    END)  AS HS_SALE_AMT_10,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 11 THEN B.SALE_AMT  
                        ELSE 0  
                    END)  AS HS_SALE_AMT_11,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 12 THEN B.SALE_AMT  
                        ELSE 0  
                    END)  AS HS_SALE_AMT_12,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 13 THEN B.SALE_AMT  
                        ELSE 0  
                    END)  AS HS_SALE_AMT_13,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 14 THEN B.SALE_AMT  
                        ELSE 0  
                    END)  AS HS_SALE_AMT_14,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 15 THEN B.SALE_AMT  
                        ELSE 0  
                    END)  AS HS_SALE_AMT_15,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 16 THEN B.SALE_AMT  
                        ELSE 0  
                    END)  AS HS_SALE_AMT_16  ,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 1 THEN B.RTN_AMT  
                        ELSE 0  
                    END)  AS HS_RTN_AMT_1,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 2 THEN B.RTN_AMT  
                        ELSE 0  
                    END)  AS HS_RTN_AMT_2,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 3 THEN B.RTN_AMT  
                        ELSE 0  
                    END)  AS HS_RTN_AMT_3,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 4 THEN B.RTN_AMT  
                        ELSE 0  
                    END)  AS HS_RTN_AMT_4,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 5 THEN B.RTN_AMT  
                        ELSE 0  
                    END)  AS HS_RTN_AMT_5,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 6 THEN B.RTN_AMT  
                        ELSE 0  
                    END)  AS HS_RTN_AMT_6,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 7 THEN B.RTN_AMT  
                        ELSE 0  
                    END)  AS HS_RTN_AMT_7,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 8 THEN B.RTN_AMT  
                        ELSE 0  
                    END)  AS HS_RTN_AMT_8,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 9 THEN B.RTN_AMT  
                        ELSE 0  
                    END)  AS HS_RTN_AMT_9,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 10 THEN B.RTN_AMT  
                        ELSE 0  
                    END)  AS HS_RTN_AMT_10,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 11 THEN B.RTN_AMT  
                        ELSE 0  
                    END)  AS HS_RTN_AMT_11,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 12 THEN B.RTN_AMT  
                        ELSE 0  
                    END)  AS HS_RTN_AMT_12,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 13 THEN B.RTN_AMT  
                        ELSE 0  
                    END)  AS HS_RTN_AMT_13  ,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 1 THEN B.SALE_QTY  
                        ELSE 0  
                    END)  AS HS_SALE_QTY_1,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 2 THEN B.SALE_QTY  
                        ELSE 0  
                    END)  AS HS_SALE_QTY_2,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 3 THEN B.SALE_QTY  
                        ELSE 0  
                    END)  AS HS_SALE_QTY_3,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 4 THEN B.SALE_QTY  
                        ELSE 0  
                    END)  AS HS_SALE_QTY_4,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 5 THEN B.SALE_QTY  
                        ELSE 0  
                    END)  AS HS_SALE_QTY_5,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 6 THEN B.SALE_QTY  
                        ELSE 0  
                    END)  AS HS_SALE_QTY_6,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 7 THEN B.SALE_QTY  
                        ELSE 0  
                    END)  AS HS_SALE_QTY_7,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 8 THEN B.SALE_QTY  
                        ELSE 0  
                    END)  AS HS_SALE_QTY_8,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 9 THEN B.SALE_QTY  
                        ELSE 0  
                    END)  AS HS_SALE_QTY_9,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 10 THEN B.SALE_QTY  
                        ELSE 0  
                    END)  AS HS_SALE_QTY_10,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 11 THEN B.SALE_QTY  
                        ELSE 0  
                    END)  AS HS_SALE_QTY_11,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 12 THEN B.SALE_QTY  
                        ELSE 0  
                    END)  AS HS_SALE_QTY_12,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 13 THEN B.SALE_QTY  
                        ELSE 0  
                    END)  AS HS_SALE_QTY_13  ,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 1 THEN B.PROF_AMT  
                        ELSE 0  
                    END)  AS HS_PROF_AMT_1,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 2 THEN B.PROF_AMT  
                        ELSE 0  
                    END)  AS HS_PROF_AMT_2,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 3 THEN B.PROF_AMT  
                        ELSE 0  
                    END)  AS HS_PROF_AMT_3,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 4 THEN B.PROF_AMT  
                        ELSE 0  
                    END)  AS HS_PROF_AMT_4,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 5 THEN B.PROF_AMT  
                        ELSE 0  
                    END)  AS HS_PROF_AMT_5,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 6 THEN B.PROF_AMT  
                        ELSE 0  
                    END)  AS HS_PROF_AMT_6,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 7 THEN B.PROF_AMT  
                        ELSE 0  
                    END)  AS HS_PROF_AMT_7,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 8 THEN B.PROF_AMT  
                        ELSE 0  
                    END)  AS HS_PROF_AMT_8,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 9 THEN B.PROF_AMT  
                        ELSE 0  
                    END)  AS HS_PROF_AMT_9,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 10 THEN B.PROF_AMT  
                        ELSE 0  
                    END)  AS HS_PROF_AMT_10,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 11 THEN B.PROF_AMT  
                        ELSE 0  
                    END)  AS HS_PROF_AMT_11,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 12 THEN B.PROF_AMT  
                        ELSE 0  
                    END)  AS HS_PROF_AMT_12,
                    SUM (  CASE  
                        WHEN A.ORD_SEQ = 13 THEN B.PROF_AMT  
                        ELSE 0  
                    END)  AS HS_PROF_AMT_13  ,
                    COUNT (  DISTINCT CASE  
                        WHEN B.SALE_QTY > 0  THEN  B.CUST_CD  
                    END)  AS TOT_CUST_CNT,
                    COUNT (  DISTINCT CASE  
                        WHEN A.ORD_SEQ = 1  
                        AND B.SALE_QTY > 0  THEN  B.CUST_CD  
                    END)  AS HS_CUST_CNT_1,
                    COUNT (  DISTINCT CASE  
                        WHEN A.ORD_SEQ = 2  
                        AND B.SALE_QTY > 0  THEN  B.CUST_CD  
                    END)  AS HS_CUST_CNT_2,
                    COUNT (  DISTINCT CASE  
                        WHEN A.ORD_SEQ = 3  
                        AND B.SALE_QTY > 0  THEN  B.CUST_CD  
                    END)  AS HS_CUST_CNT_3,
                    COUNT (  DISTINCT CASE  
                        WHEN A.ORD_SEQ = 4  
                        AND B.SALE_QTY > 0  THEN  B.CUST_CD  
                    END)  AS HS_CUST_CNT_4,
                    COUNT (  DISTINCT CASE  
                        WHEN A.ORD_SEQ = 5  
                        AND B.SALE_QTY > 0  THEN  B.CUST_CD  
                    END)  AS HS_CUST_CNT_5,
                    COUNT (  DISTINCT CASE  
                        WHEN A.ORD_SEQ = 6  
                        AND B.SALE_QTY > 0  THEN  B.CUST_CD  
                    END)  AS HS_CUST_CNT_6,
                    COUNT (  DISTINCT CASE  
                        WHEN A.ORD_SEQ = 7  
                        AND B.SALE_QTY > 0  THEN  B.CUST_CD  
                    END)  AS HS_CUST_CNT_7,
                    COUNT (  DISTINCT CASE  
                        WHEN A.ORD_SEQ = 8  
                        AND B.SALE_QTY > 0  THEN  B.CUST_CD  
                    END)  AS HS_CUST_CNT_8,
                    COUNT (  DISTINCT CASE  
                        WHEN A.ORD_SEQ = 9  
                        AND B.SALE_QTY > 0  THEN  B.CUST_CD  
                    END)  AS HS_CUST_CNT_9,
                    COUNT (  DISTINCT CASE  
                        WHEN A.ORD_SEQ = 10  
                        AND B.SALE_QTY > 0  THEN  B.CUST_CD  
                    END)  AS HS_CUST_CNT_10,
                    COUNT (  DISTINCT CASE  
                        WHEN A.ORD_SEQ = 11  
                        AND B.SALE_QTY > 0  THEN  B.CUST_CD  
                    END)  AS HS_CUST_CNT_11,
                    COUNT (  DISTINCT CASE  
                        WHEN A.ORD_SEQ = 12  
                        AND B.SALE_QTY > 0  THEN  B.CUST_CD  
                    END)  AS HS_CUST_CNT_12,
                    COUNT (  DISTINCT CASE  
                        WHEN A.ORD_SEQ = 13  
                        AND B.SALE_QTY > 0  THEN  B.CUST_CD  
                    END)  AS HS_CUST_CNT_13  
                FROM
                    (  SELECT
                        TO_CHAR (  ADD_MONTHS (  TO_DATE (  TO_CHAR (  ADD_MONTHS (SYSDATE, -15),  'YYYYMM'),  'YYYYMM'),  LEVEL - 1),  'YYYYMM')  AS STD_YM,
                        RANK ()  OVER (  
                    ORDER BY
                        TO_CHAR (  ADD_MONTHS (  TO_DATE (  TO_CHAR (  ADD_MONTHS (SYSDATE,  -15),  'YYYYMM'),  'YYYYMM'),  LEVEL - 1),  'YYYYMM') DESC)  AS ORD_SEQ  
                    FROM
                        DUAL  CONNECT 
                    BY
                        LEVEL <=  ABS (  MONTHS_BETWEEN (  TO_DATE (  TO_CHAR (  ADD_MONTHS (  SYSDATE,  -15),  'YYYYMM'),  'YYYYMM'),  TO_DATE (  TO_CHAR (SYSDATE,  'yyyymm'),  'YYYYMM')))  + 1) A  
                LEFT JOIN
                    (  SELECT
                        *  
                    FROM
                        SUB_DATA_CUST A  
                    WHERE
                        0 = 1 
                        AND A.STD_YM BETWEEN TO_CHAR (  ADD_MONTHS (  SYSDATE,  -15),  'YYYYMM')  AND TO_CHAR (SYSDATE,  'yyyymm')) B  PARTITION 
                BY
                    (  B.COMP_CD,
                    B.CUST_CD,
                    B.STD_YM,
                    B.BRND_CD,
                    B.LARG_CTG_CD)  
                        ON A.STD_YM = B.STD_YM  
                GROUP BY
                    B.COMP_CD,
                    B.CUST_CD,
                    B.BRND_CD,
                    B.LARG_CTG_CD  ) A  
                GROUP BY
                    COMP_CD,
                    CUST_CD,
                    BRND_CD,
                    LARG_CTG_CD) A  
                LEFT JOIN
                    ( SELECT
                        B.CUST_CD,  B.BRND_CD,  B.ITEM_LARG_CTG_CD,  COUNT (*) AS RECM_ITEM_CNT  
                    FROM
                        T_CTX_RECM_ITEM_BASE A  
                    INNER JOIN
                        T_CTX_RECM_BATCH_RSLT_D B  
                            ON A.PRO_COND_NM = B.PRO_COND_NM  
                            AND B.RSLT_CLS_CD = '1'   
                            AND B.CUST_CD IN ('18320', '31320')  
                    WHERE
                        0 = 1 
                        AND TO_CHAR (SYSDATE, 'YYYYMMDD') BETWEEN A.ST_DT  AND A.END_DT  
                    GROUP BY
                        B.CUST_CD, B.BRND_CD, B.ITEM_LARG_CTG_CD) CTX_RECM  
                        ON A.BRND_CD = CTX_RECM.BRND_CD  
                        AND A.LARG_CTG_CD = CTX_RECM.ITEM_LARG_CTG_CD  
                        AND A.CUST_CD = CTX_RECM.CUST_CD  
                LEFT JOIN
                    SUB_DATA_SCORE SCORE  
                        ON A.COMP_CD = SCORE.COMP_CD  
                        AND A.BRND_CD = SCORE.BRND_CD  
                        AND A.LARG_CTG_CD = SCORE.LARG_CTG_CD  
                        AND A.CUST_CD = SCORE.CUST_CD  
                ORDER BY
                    AVG_AMT DESC) T1

================================================== This is a query that is actually executed in Oracle.

When parser is run, the part where the error occurs seems to be the partition by part.

Again, I have a question. Even if there is an error, is there a way to get the statement up to the parsed part?

manticore-projects commented 2 years ago

If you would like to receive help and support, then please:

1) trim you example down to the shortest simplified illustration 2) address our concern, that PARTITION BY should be part of a Window Function -- or otherwise point out the specific Syntax specification JSQLParser was missing.

I am sorry, but the presented example does not help us in that form.

leedonet commented 2 years ago

SELECT B.COMP_CD, B.CUST_CD, B.BRND_CD, B.LARG_CTG_CD, SUM(B.SALE_AMT) AS SALE_AMT
FROM ( SELECT TO_CHAR(ADD_MONTHS (TO_DATE (TO_CHAR (ADD_MONTHS (SYSDATE, -15),'YYYYMM'),'YYYYMM'), LEVEL - 1),'YYYYMM') AS STD_YM, RANK() OVER ( ORDER BY TO_CHAR(ADD_MONTHS(TO_DATE(TO_CHAR(ADD_MONTHS(SYSDATE, -15), 'YYYYMM'), 'YYYYMM'), LEVEL - 1), 'YYYYMM') DESC) AS ORD_SEQ
FROM DUAL CONNECT BY LEVEL <= ABS (MONTHS_BETWEEN (TO_DATE (TO_CHAR (ADD_MONTHS (SYSDATE, -15), 'YYYYMM'), 'YYYYMM'), TO_DATE (TO_CHAR (SYSDATE, 'yyyymm'), 'YYYYMM'))) + 1 ) A
LEFT JOIN ( SELECT * FROM SUB_DATA_CUST A
WHERE 0 = 1 AND A.STD_YM BETWEEN TO_CHAR ( ADD_MONTHS ( SYSDATE, -15), 'YYYYMM') AND TO_CHAR (SYSDATE, 'yyyymm') ) B
PARTITION BY ( B.COMP_CD, B.CUST_CD, B.STD_YM, B.BRND_CD, B.LARG_CTG_CD )
ON A.STD_YM = B.STD_YM
GROUP BY B.COMP_CD, B.CUST_CD, B.BRND_CD, B.LARG_CTG_CD

This is a simple query. Please check again.

manticore-projects commented 2 years ago

Greetings.

I have tried to trim your query to the relevant clauses:

SELECT  b.comp_cd
        , b.cust_cd
        , b.brnd_cd
        , b.larg_ctg_cd
        , Sum( b.sale_amt ) AS sale_amt
FROM a
    LEFT JOIN b
        --PARTITION BY ( B.COMP_CD, B.CUST_CD, B.STD_YM, B.BRND_CD, B.LARG_CTG_CD )
        ON a.std_ym = b.std_ym
GROUP BY    b.comp_cd
            , b.cust_cd
            , b.brnd_cd
            , b.larg_ctg_cd
;

As we have tried to explain before, we are not familiar with your particular use of the PARTITION BY ( as part of the JOIN?!). We know it as part of a WINDOW function as shown here: https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions004.htm#SQLRF06174

image

You will need to point out which Grammar exactly shall apply as I deem your statement invalid (on Oracle DB) right now. Alternatively, please explain how exactly your statement resembles a WINDOW function.

Last but not least, please provide a self containing test case including the CREATE TABLE ..., INSERT INTO ... and SELECT ... statements in the shortest possible form.

leedonet commented 2 years ago

Thanks for the explanation above.

leedonet commented 2 years ago

Also, please check the query below.

SELECT /+ FULL(S1) / MDT_YR AS MDT_YR
,S1.UTK_NO AS UTK_NO ,S1.HTP AS HTP
,NVL(S2.RNT_ASAMT,0) AS RNT_ASAMT
,NVL(RNT_ASAMT_MDT_RT,0) AS RNT_ASAMT_MDT_RT
,NVL(S3.RNT_ASAMT,0) AS SML_ARE_RNT_ASAMT
,'N' AS FL1_SALE_APP_YN
,'N' AS DEL_YN
,SYS_REG_IDR_NO AS SYS_REG_IDR_NO
,SYS_REG_DTTM AS SYS_REG_DTTM
,SYS_CHN_IDR_NO AS SYS_CHN_IDR_NO ,SYS_CHN_DTTM AS SYS_CHN_DTTM
FROM JIMOWN.IM_HO_RNTASAMTCHTMDTRT_TT S1 , (SELECT UTK_NO,HTP,ALY_BGN_DT,ALY_ND_DT,RNT_ASAMT FROM JIMOWN.IM_HO_RNTASAMTHTPCHT_TT) S2 , (SELECT UTK_NO,HTP,ALY_BGN_DT,ALY_ND_DT,RNT_ASAMT FROM JIMOWN.IM_HO_RNTASAMTHTPCHT_TT) S3 WHERE S1.UTK_NO = S2.UTK_NO(+) AND S1.HTP = S2.HTP(+) AND TO_CHAR(TO_NUMBER(S1.MDT_YR)-2) || '0630' BETWEEN S2.ALY_BGN_DT(+) AND S2.ALY_ND_DT(+) AND S1.UTK_NO = S3.UTK_NO(+) AND S1.HTP = S3.HTP(+) AND S1 .MDT_YRI || '0630' BETWEEN S3.ALY_BGN_DT(+) AND S3.ALY_ND_DT(+)

===============================

net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "(" "(" at line 22, column 52.

Was expecting one of:

"&"
"::"
"<<"
">>"
"AND"
"COLLATE"
"["
"^"
"|"

at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:48879)
at net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:48629)
at net.sf.jsqlparser.parser.CCJSqlParser.Between(CCJSqlParser.java:13637)
at net.sf.jsqlparser.parser.CCJSqlParser.SQLCondition(CCJSqlParser.java:13382)
at net.sf.jsqlparser.parser.CCJSqlParser.Condition(CCJSqlParser.java:13014)
at net.sf.jsqlparser.parser.CCJSqlParser.AndExpression(CCJSqlParser.java:12904)
at net.sf.jsqlparser.parser.CCJSqlParser.OrExpression(CCJSqlParser.java:12779)
at net.sf.jsqlparser.parser.CCJSqlParser.XorExpression(CCJSqlParser.java:12749)
at net.sf.jsqlparser.parser.CCJSqlParser.Expression(CCJSqlParser.java:12688)
at net.sf.jsqlparser.parser.CCJSqlParser.WhereClause(CCJSqlParser.java:11632)
at net.sf.jsqlparser.parser.CCJSqlParser.PlainSelect(CCJSqlParser.java:7536)
at net.sf.jsqlparser.parser.CCJSqlParser.SetOperationList(CCJSqlParser.java:7825)
at net.sf.jsqlparser.parser.CCJSqlParser.SelectBody(CCJSqlParser.java:7340)
at net.sf.jsqlparser.parser.CCJSqlParser.Select(CCJSqlParser.java:7327)
at net.sf.jsqlparser.parser.CCJSqlParser.SingleStatement(CCJSqlParser.java:315)
at net.sf.jsqlparser.parser.CCJSqlParser.Statement(CCJSqlParser.java:169)
manticore-projects commented 2 years ago

This part is invalid:

 BETWEEN S2.ALY_BGN_DT(+) AND S2.ALY_ND_DT(+)

Although I will appreciate when you won't mix completely unrelated questions into one issue.

1) has the question about PARTITION BY been answered, can we close this case? 2) would you like to test any other statement first and ensure that your syntax is actually valid please?

Please use JSQLFormatter ONLINE to visualize your statements and parsing errors.

leedonet commented 2 years ago

Although I will appreciate when you won't mix completely unrelated questions into one issue. => Okay. thank you

  1. has the question about PARTITION BY been answered, can we close this case? => I will comment after reconfirming.
  2. would you like to test any other statement first and ensure that your syntax is actually valid please? => This is a query used in real work.
manticore-projects commented 2 years ago

2. would you like to test any other statement first and ensure that your syntax is actually valid please? => This is a query used in real work.

Pardon me for being blunt: I do not understand how that would work because the Oracle BETWEEN Expression is defined like this: https://docs.oracle.com/cd/E11882_01/server.112/e41084/conditions011.htm#SQLRF52164

image

Where do the (+) operators come in here?

leedonet commented 2 years ago

Can I ask you another question?

Even if ParserException occurs using JSqlParser Can I get SelectItems? Is there a way to extract only column information?

manticore-projects commented 2 years ago

Even if ParserException occurs using JSqlParser Can I get SelectItems? Is there a way to extract only column information?

Although there is an ErrorRecovery feature, it seems to apply as per statement only -- meaning, the parser will move forward to the NEXT statement, when it fails to parse an erroneous statement. The erroneous statement is still lost. So the answer is likely No.

manticore-projects commented 2 years ago

No feedback and no further questions asked. Closed.