Below error occurs in SQL Optimizer for Oracle when optimizing a valid Select statement query:
"The Parser did not identify the text as a valid SQL statement."
Query runs fine in Toad and SQLPlus.
Query statement has an unsupported syntax in the SQL Optimizer engine.
The right supported query format for the SQL Optimizer engine is for the parenthesis around the table name of an INNER JOIN clause and for aliasing the PIVOT columns. For example:
- INNER JOIN ( <table name> ) <table name2> ON (....)
- With (Select org_price_zone_1, ...
GROUP BY xvpz.vendor_id, xvpz.vendor_site_id, xvpz.price_zone)
PIVOT (MIN(org_price_zone) FOR
price_zone IN (1 as org_price_zone_1, 2 as org_price_zone_2))
Select org_price_zone_1, ...
Solution:
Replace a valid query formatting to SQL Optimizer for the INNER JOIN and alias the PIVOT columns.
INNER JOIN <table name> <table name2> ON (....)
With (Select org_price_zone_1 as price_zone_1, ...
GROUP BY xvpz.vendor_id, xvpz.vendor_site_id, xvpz.price_zone)
PIVOT (MIN(org_price_zone) FOR
price_zone IN (1 as org_price_zone_1, 2 as org_price_zone_2))
Select price_zone_1, ...
