Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Parsing folder problem with parantheses #179

Open
stoch opened this issue Sep 19, 2019 · 0 comments
Open

Parsing folder problem with parantheses #179

stoch opened this issue Sep 19, 2019 · 0 comments
Assignees
Labels

Comments

@stoch
Copy link
Contributor

stoch commented Sep 19, 2019

The following SQL is changed in DDerl to something which breaks the Oracle parser:

    SELECT   /*+ NO_INDEX(BDETAIL2) */
             TO_CHAR (ADD_MONTHS (SYSDATE, -1), 'YYYYMM'),
             CON_CODE,
             COUNT(*),
             SUM (BD_IW_AMOUNT)/COUNT(*),
             SUM (BD_IW_AMOUNT),
             BD_IW_CURID
    FROM     BDETAIL2, CONTRACT
    WHERE    CON_ID = BD_TOCID
             AND CON_ESID = 'A'
             AND CON_ID <> 'FIXNETTOC'
             AND NVL (CON_VIRTUAL, 0) = 0
             AND BD_DATETIME >= ADD_MONTHS (TRUNC(SYSDATE,'MONTH'), -1)
             AND BD_DATETIME < ADD_MONTHS (TRUNC(SYSDATE,'MONTH'), 0)
             AND BD_MAPSID = 'R'
             AND BD_CDRTID NOT IN ('SMS-EXT','PAGER-EXT')
             AND BD_STATUS = '0'
             AND BD_IW = '1'
             AND BD_IW_DIR = 'O'
    GROUP BY CON_CODE, BD_IW_CURID
    UNION ALL
    SELECT   TO_CHAR (ADD_MONTHS (SYSDATE, -1), 'YYYYMM'),
             CON_CODE,
             0 smscount,
             ciote_price + 0.0,
             0 amount,
             ciot_curid
    FROM     contract,
             coniot,
             coniote
    WHERE    CON_ESID = 'A'
             AND CON_ETID = 'TOC'
             AND CON_ID <> 'FIXNETTOC'
             AND NVL (CON_VIRTUAL, 0) = 0
             AND con_datestart < ADD_MONTHS (TRUNC (SYSDATE, 'MONTH'), 0)
             AND (con_dateend > ADD_MONTHS (TRUNC (SYSDATE, 'MONTH'), -1) OR con_dateend IS NULL)
             AND NVL (CON_VIRTUAL, 0) = 0
             AND ciot_id = ciote_ciotid
             AND ciot_conid = con_id
             AND ciot_trctid IN ('SMS')
             AND ciot_iwdid IN ('ORIG')
             AND NOT EXISTS
                (SELECT ROWID FROM BDETAIL2
                 WHERE  CON_ID = BD_TOCID
                 AND BD_DATETIME >= ADD_MONTHS (TRUNC(SYSDATE,'MONTH'), -1)
                 AND BD_DATETIME < ADD_MONTHS (TRUNC(SYSDATE,'MONTH'), 0)
                 AND BD_MAPSID = 'R'
                 AND BD_CDRTID NOT IN ('SMS-EXT','PAGER-EXT')
                 AND BD_STATUS = '0'
                 AND BD_IW = '1'
                 AND BD_IW_DIR = 'O')
    GROUP BY CON_CODE,
             ciote_price,
             ciot_curid
    ORDER BY
        1,
        2,
        4,
        6

is corrupted into :

    (SELECT /*+ NO_INDEX(BDETAIL2) */
        TO_CHAR(ADD_MONTHS(SYSDATE, - 1), 'YYYYMM'), CON_CODE, COUNT(*),
        SUM(BD_IW_AMOUNT) / COUNT(*), SUM(BD_IW_AMOUNT), BD_IW_CURID
    FROM
        BDETAIL2,
        CONTRACT
    WHERE
        CON_ID = BD_TOCID
        AND CON_ESID = 'A'
        AND CON_ID <> 'FIXNETTOC'
        AND NVL(CON_VIRTUAL, 0) = 0
        AND BD_DATETIME >= ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), - 1)
        AND BD_DATETIME < ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), 0)
        AND BD_MAPSID = 'R'
        AND NOT (BD_CDRTID IN ('SMS-EXT', 'PAGER-EXT'))
        AND BD_STATUS = '0'
        AND BD_IW = '1'
        AND BD_IW_DIR = 'O'
    GROUP BY
        CON_CODE, BD_IW_CURID)
UNION ALL
    (SELECT
        TO_CHAR(ADD_MONTHS(SYSDATE, - 1), 'YYYYMM'), CON_CODE, 0 smscount,
        ciote_price + 0.0, 0 amount, ciot_curid
    FROM
        contract,
        coniot,
        coniote
    WHERE
        CON_ESID = 'A'
        AND CON_ETID = 'TOC'
        AND CON_ID <> 'FIXNETTOC'
        AND NVL(CON_VIRTUAL, 0) = 0
        AND con_datestart < ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), 0)
        AND (con_dateend > ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), - 1)
        OR con_dateend IS NULL)
        AND NVL(CON_VIRTUAL, 0) = 0
        AND ciot_id = ciote_ciotid
        AND ciot_conid = con_id
        AND ciot_trctid IN ('SMS')
        AND ciot_iwdid IN ('ORIG')
        AND NOT (EXISTS
        (SELECT
            ROWID
        FROM
            BDETAIL2
        WHERE
            CON_ID = BD_TOCID
            AND BD_DATETIME >= ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), - 1)
            AND BD_DATETIME < ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), 0)
            AND BD_MAPSID = 'R'
            AND NOT (BD_CDRTID IN ('SMS-EXT', 'PAGER-EXT'))
            AND BD_STATUS = '0'
            AND BD_IW = '1'
            AND BD_IW_DIR = 'O'))
    GROUP BY
        CON_CODE, ciote_price, ciot_curid
    ORDER BY
        1, 2, 4, 6)

If adding parantheses is really neccessary, then the order by clause must be outside of it.

@stoch stoch added the bug label Sep 19, 2019
@walter-weinmann walter-weinmann changed the title Parsing folder problem with paratheses Parsing folder problem with parantheses May 29, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Projects
None yet
Development

No branches or pull requests

2 participants