Skip to content

Parsing folder problem with parantheses  #179

@stoch

Description

@stoch

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.

Metadata

Metadata

Labels

Type

No type

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions