Hello,
I have encountered an annoying issue after upgrading my Toad For Oracle to 12.9 from 12.8. I have solved the issue but if the query runs as is in native sqlplus and in previous versions it should run here too. I posting this for Toad support and other sql coders that might need to fix similar queries or code similar statements.
I have a unioned query similar to the simplified version below. I simply go out to several databases through database links and look for employees that might exist in each.
I give each column a title and wrap the all the union query with a “select” using the titles of each column. I do this so I can add filters to the where clause like what you see below.
That way I can apply additional criteria to each set of database results without adding to each database’s where clause. I have five unioned queries so this format saves me a lot of time.
I have been running this query for years and it still runs in 12.8.
The syntax error Toad gives in 12.9 is in the blank line before the first union statement is "expecting -or- WITH -or- Fetch Offset -or- ORDER -or Intersect Minus Union…
The solution is to remove ALL blank lines in the unioned statements between the parens and even the additional where clause following the closing paren of the unioned statements.
I like to use the blank line to better visually isolate each query for maintaining. Also whitespace and blank lines are ANSIi sql compliant and should work without modification.
I would hope that Toad Development confirms and provides a patch that remedies this “false” syntax error.
select “NAME”, “DEPT”
from
(select emp_name “NAME”, emp_dept “DEPT”
from SCHEMA1.employee
where upper(emp_name) like ‘%&&NAME%’
union
select emp_name “NAME”, emp_dept “DEPT”
from SCHEMA1.employee@DB_LINK
where upper(emp_name) like ‘%&&NAME%’
)
where 1 = 1
and “DEPT” not in (‘TEMPLATE’)