Toad World® Forums

Code Formatter Incorrect Formatting - 12.9.0.66

The Toad code formatter in Toad for Oracle beta version 12.9.0.66 is producing unexpected results.

In all earlier versions of Toad, my SQL statement is formatted like this:

SELECT TO_DATE(‘05/21/2016’, ‘MM/DD/YYYY’) AS CICS_EXTRACT_DATE,
TO_DATE(‘05/22/2016’, ‘MM/DD/YYYY’) AS M204_EXTRACT_DATE,
r.*
FROM (SELECT mv.*
FROM ECORP2_STAGE.MV_AMEND_DISCONTINUE mv
WHERE TYPEA IN (‘91’, ‘94’, ‘97’) AND
NOT EXISTS
(SELECT RECKEY
FROM ECORP2_STAGE.STAGE_ASSUMED_NAME san
WHERE san.RECKEY = mv.OTFILM)) r
ORDER BY CNAME ASC,
CICS_FILDT ASC,
TYPEA ASC,
TNAME ASC;

In Toad for Oracle beta, the code formatter produces the following incorrrect result for the same SQL statement:

SELECT TO_DATE(‘05/21/2016’, ‘MM/DD/YYYY’) AS CICS_EXTRACT_DATE, TO_DATE(‘05/22/2016’, ‘MM/DD/YYYY’) AS M204_EXTRACT_DATE, r.*
FROM (SELECT mv.*
FROM ECORP2_STAGE.MV_AMEND_DISCONTINUE mv
WHERE TYPEA IN (‘91’, ‘94’, ‘97’) AND
NOT EXISTS
(SELECT RECKEY
FROM ECORP2_STAGE.STAGE_ASSUMED_NAME san
WHERE san.RECKEY = mv.OTFILM)) r
ORDER BY CNAME ASC,
CICS_FILDT ASC,
TYPEA ASC,
TNAME ASC;

Here is another example.

Correct formatting:

SELECT TO_DATE(‘05/21/2016’, ‘MM/DD/YYYY’) AS CICS_EXTRACT_DATE,
TO_DATE(‘05/22/2016’, ‘MM/DD/YYYY’) AS M204_EXTRACT_DATE,
r.*
FROM (SELECT san.stage_assumed_name_id, san_child.stage_assumed_name_id AS child_id, NULL AS parent_id,
NULL AS parent_tname, NULL AS parent_taddr1, NULL AS parent_taddr2,
NULL AS parent_tcity, NULL AS parent_tstate, NULL AS parent_tzip,
san.RECKEY, san.TYPEA, san.CICS_FILING_GROUP,
san.CICS_ENTITY_CLASS, san.CICS_FILDT, san.OTFILM,
san.CFILM, san.CNAME, san.TNAME
FROM ECORP2_STAGE.STAGE_ASSUMED_NAME san
LEFT JOIN ECORP2_STAGE.STAGE_ASSUMED_NAME san_child ON san_child.OTFILM = san.RECKEY
WHERE san.TYPEA IN (‘90’, ‘93’, ‘96’)
UNION ALL
SELECT san.stage_assumed_name_id, san_child.stage_assumed_name_id AS child_id,
san_parent.stage_assumed_name_id AS parent_id, san_parent.tname AS parent_tname,
san_parent.taddr1 AS parent_taddr1, san_parent.taddr2 AS parent_taddr2,
san_parent.tcity AS parent_tcity, san_parent.tstate AS parent_tstate,
san_parent.tzip AS parent_tzip, san.RECKEY,
san.TYPEA, san.CICS_FILING_GROUP,
san.CICS_ENTITY_CLASS, san.CICS_FILDT,
san.OTFILM, san.CFILM,
san.CNAME, san.TNAME
FROM ECORP2_STAGE.STAGE_ASSUMED_NAME san
LEFT JOIN ECORP2_STAGE.STAGE_ASSUMED_NAME san_child ON san_child.OTFILM = san.RECKEY
LEFT JOIN ECORP2_STAGE.STAGE_ASSUMED_NAME san_parent ON san.OTFILM = san_parent.RECKEY
WHERE san.TYPEA NOT IN (‘90’, ‘93’, ‘96’)) r
WHERE (parent_id IS NOT NULL OR child_id IS NOT NULL)
ORDER BY stage_assumed_name_id ASC;

Toad for Oracle beta formatter with incorrect formatting:

SELECT TO_DATE(‘05/21/2016’, ‘MM/DD/YYYY’) AS CICS_EXTRACT_DATE, TO_DATE(‘05/22/2016’, ‘MM/DD/YYYY’) AS M204_EXTRACT_DATE, r.*
FROM (SELECT san.stage_assumed_name_id,
san_child.stage_assumed_name_id AS child_id,
NULL AS parent_id,
NULL AS parent_tname,
NULL AS parent_taddr1,
NULL AS parent_taddr2,
NULL AS parent_tcity,
NULL AS parent_tstate,
NULL AS parent_tzip,
san.RECKEY,
san.TYPEA,
san.CICS_FILING_GROUP,
san.CICS_ENTITY_CLASS,
san.CICS_FILDT,
san.OTFILM,
san.CFILM,
san.CNAME,
san.TNAME
FROM ECORP2_STAGE.STAGE_ASSUMED_NAME san
LEFT JOIN ECORP2_STAGE.STAGE_ASSUMED_NAME san_child ON san_child.OTFILM = san.RECKEY
WHERE san.TYPEA IN (‘90’, ‘93’, ‘96’)
UNION ALL
SELECT san.stage_assumed_name_id,
san_child.stage_assumed_name_id AS child_id,
san_parent.stage_assumed_name_id AS parent_id,
san_parent.tname AS parent_tname,
san_parent.taddr1 AS parent_taddr1,
san_parent.taddr2 AS parent_taddr2,
san_parent.tcity AS parent_tcity,
san_parent.tstate AS parent_tstate,
san_parent.tzip AS parent_tzip,
san.RECKEY,
san.TYPEA,
san.CICS_FILING_GROUP,
san.CICS_ENTITY_CLASS,
san.CICS_FILDT,
san.OTFILM,
san.CFILM,
san.CNAME,
san.TNAME
FROM ECORP2_STAGE.STAGE_ASSUMED_NAME san
LEFT JOIN ECORP2_STAGE.STAGE_ASSUMED_NAME san_child ON san_child.OTFILM = san.RECKEY
LEFT JOIN ECORP2_STAGE.STAGE_ASSUMED_NAME san_parent ON san.OTFILM = san_parent.RECKEY
WHERE san.TYPEA NOT IN (‘90’, ‘93’, ‘96’)) r
WHERE (parent_id IS NOT NULL OR child_id IS NOT NULL)
ORDER BY stage_assumed_name_id ASC;

I have attached my FmtPlus.opt file to this post.
FmtPlus.zip (659 Bytes)

There seems to be a regression on Column Adjusted Tabular lists since version 5.292 of parser/formatter indeed. Defect queued as QP-1649.

Thanks for reporting,

Andre