Beautifier - text not parsed

[braykov]

The beautifier can not parse a statement with more than 2 selects combined with a UNION clause.

 

[Stew Stryker]

It's always had trouble parsing UNIONs, sadly.

 

[rbrooker]

I have had three beautify successfully.....

select * from dual where 1 = 1 union all
select * from dual where 1 = 2 union all
select * from dual where 2 = 2

becomes

SELECT *
FROM   dual
WHERE  1 = 1
UNION ALL
SELECT *
FROM   dual
WHERE  1 = 2
UNION ALL
SELECT *
FROM   dual
WHERE  2 = 2

which is correct for my beautifier rules...

using union instead of union all also works

 

[braykov]

Well my query is a bit complex and I can not paste it here.

 

[rbrooker]

sugests tho' some other part of the query is causing the failure, not the unions...

 

[rbrooker]

heres my query that beautifies ok..

SELECT major,
                   minor,
                   page_id,
                   page_title,
                   page_url,
                   lead(major) over(ORDER BY major, decode(minor, 'null', 1, 2), minor, decode(page_title, 'null', 1, 2), page_id) next_major,
                   lead(minor) over(ORDER BY major, decode(minor, 'null', 1, 2), minor, decode(page_title, 'null', 1, 2), page_id) next_minor,
                   nvl(lead(page_url) over(ORDER BY major, decode(minor, 'null', 1, 2), minor, decode(page_title, 'null', 1, 2), page_id), 'null') next_url,
                   rownum row_number
            FROM (SELECT major,
                         minor,
                         page_id,
                         page_title,
                         decode(page_url, NULL, 'null', page_url) page_url
                  FROM (SELECT *
                        FROM (SELECT DISTINCT major,
                                              'null' minor,
                                              NULL sort_order,
                                              major page_id,
                                              'null' page_title,
                                              '' page_url
                              FROM vuw_help_pages
                              WHERE major IS NOT NULL
                              AND minor IS NULL
                              AND major ! = 'INTERNAL'
                              UNION ALL
                              SELECT DISTINCT major,
                                              minor,
                                              NULL sort_order,
                                              minor page_id,
                                              'null' page_title,
                                              '' page_url
                              FROM vuw_help_pages
                              WHERE major IS NOT NULL
                              AND minor IS NOT NULL
                              AND major ! = 'INTERNAL'
                              UNION ALL
                              SELECT major,
                                     decode(minor, NULL, 'null', minor),
                                     sort_order,
                                     page_id,
                                     page_title,
                                     'RELV10.VUW_HELP.HLP_NAVIGATE?pi_page=' || page_id page_url
                              FROM vuw_help_pages
                              WHERE nvl(major, 'NULL') ! = 'INTERNAL')
                        ORDER BY major,
                                 decode(minor, 'null', 1, 2),
                                 minor)) a;