org.h2.jdbc.JdbcSQLException: Duplicate column name AUDIT_EVENT_ID; SQL statement

[jclar...@gmail.com]

We have a legacy Oracle query I'm trying to get working with H2. It's
fairly complicated so I've dumbed it down a little so that I still get
the same error:

org.h2.jdbc.JdbcSQLException: Duplicate column name AUDIT_EVENT_ID;
SQL statement

I've provided DDL and a select statement that causes the error. Any
help would be appreciated.

Thanks,
Jim

create table F
(
id numeric not null
);

create table AE
(
audit_event_id NUMERIC NOT NULL
);

create table AD
(
audit_event_id NUMERIC NOT NULL
);

select F.id from (F left join
(select * from AE inner join AD on AE.audit_event_id =
AD.audit_event_id) on F.id = target);

[Thomas Mueller]

Hi,

I ran the query in H2, MySQL, and PostgreSQL, and it doesn't work in any of those databases.

MySQL: Duplicate column name 'audit_event_id'
PostgreSQL: column reference "audit_event_id" is ambiguous

For compatibility, I suggest to use:

select F.id from  (F left join

 (select AE.audit_event_id  from AE inner join AD on AE.audit_event_id =
AD.audit_event_id) X on F.id = X.audit_event_id );


Regards,
Thomas

[jclar...@gmail.com]

Ok, thanks for the multiple checks.

Oracle 10g must be doing something "special" to support the join.

Jim

On Feb 2, 1:35 am, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote: