KonnexionsGmbH / KonnexionsGmbH/sqlparse

Parsing folder problem with parantheses

Open
#179 0 comments 0 reactions 1 assignee View on GitHub

@walter-weinmann is already working on this.

Since Sep 19, 2019.

bug
Dominant language
Erlang
Stars
46
Forks
15
PR merge metrics
No merged PRs in 30d

Description

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.

Contributor guide

No contributing guide indexed for this repository

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.