Hello,
It seems that the join conditions are not at the right place:
from
STUFF S
inner join STUFF_DETAILS SD on S.ID <http://s.id/> = SD.ID
<http://sd.id/>
inner join STUFF_MORE_DETAILS SMD on SD.ID <http://sd.id/> = SMD.ID
<http://smd.id/>
Regards,
Guillaume
Le mer. 29 déc. 2021, 11:50, Oleg Kalnichevski <[email protected]>
a écrit :
> Folks,
>
> Please consider the following schema. There is nothing really special
> about it.
> ```
> create table STUFF (
> ID number(19) generated by default as identity (start with 1 increment
> by 1),
> NAME varchar2(128) not null,
> constraint PK_STUFF primary key (ID),
> constraint BK_STUFF unique (NAME)
> );
>
> create table STUFF_DETAILS (
> ID number(19) generated by default as identity (start with 1 increment
> by 1),
> BLAH varchar2(128) not null,
> constraint PK_STUFF_DETAILS primary key (ID)
> );
> create table STUFF_MORE_DETAILS (
> ID number(19) generated by default as identity (start with 1 increment
> by 1),
> BLAH_BLAH varchar2(128) not null,
> constraint PK_STUFF_MORE_DETAILS primary key (ID)
> );
> ```
> Based on the schema above the following view can be successfully created
> ```
> create or replace view V_STUFF1
> (
> ID,
> NAME,
> BLAH,
> BLAH_BLAH
> )
> as select
> S.ID,
> S.NAME,
> SD.BLAH,
> SMD.BLAH_BLAH
> from
> STUFF S
> inner join STUFF_DETAILS SD
> inner join STUFF_MORE_DETAILS SMD
> on SD.ID = SMD.ID
> on S.ID = SD.ID
> ;
> ```
> A very similar view definition, however, fails
> ```
> create or replace view V_STUFF2
> (
> ID,
> NAME,
> BLAH,
> BLAH_BLAH
> )
> as select
> S.ID,
> S.NAME,
> SD.BLAH,
> SMD.BLAH_BLAH
> from
> STUFF S
> inner join STUFF_DETAILS SD
> left outer join STUFF_MORE_DETAILS SMD
> on SD.ID = SMD.ID
> on S.ID = SD.ID
> ;
> ```
> Exception: org.h2.jdbc.JdbcSQLSyntaxErrorException: Column "SD.ID" not
> found
>
> I am perfectly aware that nested joins are ugly and the view definitions
> could be rewritten to avoid the problem.
>
> Having said that it seems wrong that H2 behaves differently depending on
> the join type (inner vs left outer) in this very particular case.
>
> It looks like a defect to me.
>
> Oleg
>
> --
> You received this message because you are subscribed to the Google Groups
> "H2 Database" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected].
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/h2-database/d6fff525-b6b9-4c58-98ea-879f0c83513dn%40googlegroups.com
> <https://groups.google.com/d/msgid/h2-database/d6fff525-b6b9-4c58-98ea-879f0c83513dn%40googlegroups.com?utm_medium=email&utm_source=footer>
> .
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/CAHLf0iC8WG-b%3DVLhi0znkToG3ZW%3D3RzAT%3D9%3DKP82Xxis32sRTg%40mail.gmail.com.
