KonnexionsGmbH / KonnexionsGmbH/sqlparse
Parsing folder problem with parantheses
Open
@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
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Assessment
This issue has not been assessed yet.