I'm using DOA Version 4.0.6.1 (August 23, 2004)
In my scripts using subquery factoring clause:
WITH query_name AS ( subquery ) [, query_name AS ( subquery ) ]...
for example: OracleScript.Lines.Text :=
WITH
dept_costs AS (
SELECT department_name, SUM(salary) dept_total
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY department_name),
avg_cost AS (
SELECT SUM(dept_total)/COUNT(*) avg
FROM dept_costs)
SELECT * FROM dept_costs
WHERE dept_total >
(SELECT avg FROM avg_cost)
ORDER BY department_name;
In function TOracleScript.SQLCommand for SQL command 'WITH' check condition not added into source code.
// Is it a SQL Command?
function TOracleScript.SQLCommand(const S1, S2: string): Boolean;
begin
...
Result := ...(S1 = 'CALL') or
(S1 = 'WITH'); //not added :-(
...
end;
Without this check (S1 = 'WITH') OracleScript has two command instead of only one:
first command:
WITH
dept_costs AS (
second command:
SELECT department_name, SUM(salary) dept_total
...
But should be only one command.
Tell please to what new version of DOA i must upgrated where this check is exists.
At this moment this check i add manually in to source Oracle.pas.
Thanks.