Markus, glad to help.
Though I admit, it seems a bit odd that MaxDB would throw an error on
what is essentially an identity parameter in a select statement.
Does this also fail on MaxDB? I just tried on Oracle 10.1.0.4.0 and
both were no issue...
select emp_id from cmp3_salary where 1=1;
select a.emp_id from cmp3_salary a, cmp3_salary b where a.emp_id = b.emp_id;
I won't claim to be a SQL spec guru, but it seems like this should be
OK... I'd expect the identity params would be internally removed by
the sql planning tools etc before the select is performed against the
table, so in effect, they would have no effect at all on the sql
results.
Wayne
On 10/13/06, Markus KARG <markus.karg_at_gmx.net> wrote:
> Wayne,
>
> sorry for answering so late, but I didn't find any time for my MaxDB
> work in the past week. Please find my answer inlined.
> > Just noticed something odd in that first error SQL statement:
> > DELETE FROM CMP3_SALARY WHERE EXISTS(SELECT t0.EMP_ID FROM
> > CMP3_EMPLOYEE t0, CMP3_SALARY t1 WHERE ((t0.F_NAME = ?) AND (t1.EMP_ID
> > = t0.EMP_ID)) AND t1.EMP_ID = CMP3_SALARY.EMP_ID)
> >
> > Specifically:
> > ... FROM CMP3_SALARY t1 WHERE ... t1.EMP_ID = CMP3_SALARY.EMP_ID
> > We've declared t1 to be an alias for CMP3_SALARY, so we're saying
> > WHERE t1.EMP_ID = t1.EMP_ID, which is unnecessary at best.
> >
> > Does this SQL run in MaxDB, assuming you're able to get the tables and
> > data set up and can run the command directly in MaxDB, but then it
> > fails in the testing? Or is the statement just completely invalid SQL
> > in MaxDB?
> Seems you are a very watchful observer! Actually what you detected is
> the cause of the problem. I copied the DELETE command into a short JDBC
> sample and let it run against the database (that still contains the
> schema BTW, so it seems the tests are not tearing down very well the
> schema they constructed in case of failing tests). The SQL ends up with
> the same error message. Then just I removed trailing "t1.EMP_ID =
> t1.EMP_ID", as it is unnecessary as you said. The SQL then runs without
> any problem (at least in my test program)!
>
> Thank you for your tip! I will ask Tom to fix that! :-)
>
> Markus
>
>
>