Hello,
> Why do you have all of those "on 1=1" statements?
My SQL is this.
from
(((((
TEST_GROUP_CLASS GC
left join TEST_GROUPM GI1 on GC.GCL_SID1 = GI1.GRP_SID)
left join TEST_GROUPM GI2 on GC.GCL_SID2 = GI2.GRP_SID)
left join TEST_GROUPM GI3 on GC.GCL_SID3 = GI3.GRP_SID)
left join TEST_GROUPM GI4 on GC.GCL_SID4 = GI4.GRP_SID)
left join TEST_GROUPM GI5 on GC.GCL_SID5 = GI5.GRP_SID)
)
"on 1 = 1" is interpreted as what?
> That's not valid SQL.
Why this SQL do you work?
select
GC.GCL_SID1 as GCL_SID1,
GC.GCL_SID2 as GCL_SID2,
GC.GCL_SID3 as GCL_SID3,
GC.GCL_SID4 as GCL_SID4,
GC.GCL_SID5 as GCL_SID5,
GI1.GRP_NAME as GNAME1,
GI2.GRP_NAME as GNAME2,
GI3.GRP_NAME as GNAME3,
GI4.GRP_NAME as GNAME4,
GI5.GRP_NAME as GNAME5
from
(((((
TEST_GROUP_CLASS GC
left join TEST_GROUPM GI1 on GC.GCL_SID1 = GI1.GRP_SID)
left join TEST_GROUPM GI2 on GC.GCL_SID2 = GI2.GRP_SID)
left join TEST_GROUPM GI3 on GC.GCL_SID3 = GI3.GRP_SID)
left join TEST_GROUPM GI4 on GC.GCL_SID4 = GI4.GRP_SID)
left join TEST_GROUPM GI5 on GC.GCL_SID5 = GI5.GRP_SID)
"Select count (*) from" I just delete the.
And h2 1.2 to work.
On 5月12日, 午後7:38, Noel Grandin <[email protected]> wrote:
> Why do you have all of those "on 1=1" statements?
> That's not valid SQL.
>
>
>
>
>
>
>
> HK wrote:
> > Hello,
>
> > I'll write an error message.
>
> > Exception in thread "main" org.h2.jdbc.JdbcSQLException: Syntax error
> > in SQL statement "SELECT
> > GC.GCL_SID1 AS GCL_SID1,
> > GC.GCL_SID2 AS GCL_SID2,
> > GC.GCL_SID3 AS GCL_SID3,
> > GC.GCL_SID4 AS GCL_SID4,
> > GC.GCL_SID5 AS GCL_SID5,
> > GI1.GRP_NAME AS GNAME1,
> > GI2.GRP_NAME AS GNAME2,
> > GI3.GRP_NAME AS GNAME3,
> > GI4.GRP_NAME AS GNAME4,
> > GI5.GRP_NAME AS GNAME5
> > FROM (
> > (
> > (
> > (
> > (
> > PUBLIC.TEST_GROUP_CLASS GC
> > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI1
> > ON GC.GCL_SID1 = GI1.GRP_SID
> > ) ON[*] 1=1
> > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI2
> > ON GC.GCL_SID2 = GI2.GRP_SID
> > ) ON 1=1
> > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI3
> > ON GC.GCL_SID3 = GI3.GRP_SID
> > ) ON 1=1
> > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI4
> > ON GC.GCL_SID4 = GI4.GRP_SID
> > ) ON 1=1
> > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI5
> > ON GC.GCL_SID5 = GI5.GRP_SID
> > ) ON 1=1
> > INNER JOIN (
> > (
> > (
> > (
> > PUBLIC.TEST_GROUP_CLASS GC
> > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI1
> > ON GC.GCL_SID1 = GI1.GRP_SID
> > ) ON 1=1
> > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI2
> > ON GC.GCL_SID2 = GI2.GRP_SID
> > ) ON 1=1
> > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI3
> > ON GC.GCL_SID3 = GI3.GRP_SID
> > ) ON 1=1
> > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI4
> > ON GC.GCL_SID4 = GI4.GRP_SID
> > ) ON 1=1
> > INNER JOIN (
> > (
> > (
> > PUBLIC.TEST_GROUP_CLASS GC
> > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI1
> > ON GC.GCL_SID1 = GI1.GRP_SID
> > ) ON 1=1
> > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI2
> > ON GC.GCL_SID2 = GI2.GRP_SID
> > ) ON 1=1
> > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI3
> > ON GC.GCL_SID3 = GI3.GRP_SID
> > ) ON 1=1
> > INNER JOIN (
> > (
> > PUBLIC.TEST_GROUP_CLASS GC
> > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI1
> > ON GC.GCL_SID1 = GI1.GRP_SID
> > ) ON 1=1
> > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI2
> > ON GC.GCL_SID2 = GI2.GRP_SID
> > ) ON 1=1
> > INNER JOIN (
> > PUBLIC.TEST_GROUP_CLASS GC
> > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI1
> > ON GC.GCL_SID1 = GI1.GRP_SID
> > ) ON 1=1
> > INNER JOIN PUBLIC.TEST_GROUP_CLASS GC
> > ON 1=1
> > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI1
> > ON GC.GCL_SID1 = GI1.GRP_SID
> > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI2
> > ON GC.GCL_SID2 = GI2.GRP_SID
> > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI3
> > ON GC.GCL_SID3 = GI3.GRP_SID
> > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI4
> > ON GC.GCL_SID4 = GI4.GRP_SID
> > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI5
> > ON GC.GCL_SID5 = GI5.GRP_SID"; expected "AS, RIGHT, LEFT, FULL,
> > INNER, JOIN, CROSS, NATURAL, )"; SQL statement:
> > CREATE FORCE VIEW PUBLIC._0 AS
> > SELECT
> > GC.GCL_SID1 AS GCL_SID1,
> > GC.GCL_SID2 AS GCL_SID2,
> > GC.GCL_SID3 AS GCL_SID3,
> > GC.GCL_SID4 AS GCL_SID4,
> > GC.GCL_SID5 AS GCL_SID5,
> > GI1.GRP_NAME AS GNAME1,
> > GI2.GRP_NAME AS GNAME2,
> > GI3.GRP_NAME AS GNAME3,
> > GI4.GRP_NAME AS GNAME4,
> > GI5.GRP_NAME AS GNAME5
> > FROM (
> > (
> > (
> > (
> > (
> > PUBLIC.TEST_GROUP_CLASS GC
> > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI1
> > ON GC.GCL_SID1 = GI1.GRP_SID
> > ) ON 1=1
> > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI2
> > ON GC.GCL_SID2 = GI2.GRP_SID
> > ) ON 1=1
> > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI3
> > ON GC.GCL_SID3 = GI3.GRP_SID
> > ) ON 1=1
> > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI4
> > ON GC.GCL_SID4 = GI4.GRP_SID
> > ) ON 1=1
> > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI5
> > ON GC.GCL_SID5 = GI5.GRP_SID
> > ) ON 1=1
> > INNER JOIN (
> > (
> > (
> > (
> > PUBLIC.TEST_GROUP_CLASS GC
> > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI1
> > ON GC.GCL_SID1 = GI1.GRP_SID
> > ) ON 1=1
> > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI2
> > ON GC.GCL_SID2 = GI2.GRP_SID
> > ) ON 1=1
> > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI3
> > ON GC.GCL_SID3 = GI3.GRP_SID
> > ) ON 1=1
> > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI4
> > ON GC.GCL_SID4 = GI4.GRP_SID
> > ) ON 1=1
> > INNER JOIN (
> > (
> > (
> > PUBLIC.TEST_GROUP_CLASS GC
> > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI1
> > ON GC.GCL_SID1 = GI1.GRP_SID
> > ) ON 1=1
> > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI2
> > ON GC.GCL_SID2 = GI2.GRP_SID
> > ) ON 1=1
> > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI3
> > ON GC.GCL_SID3 = GI3.GRP_SID
> > ) ON 1=1
> > INNER JOIN (
> > (
> > PUBLIC.TEST_GROUP_CLASS GC
> > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI1
> > ON GC.GCL_SID1 = GI1.GRP_SID
> > ) ON 1=1
> > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI2
> > ON GC.GCL_SID2 = GI2.GRP_SID
> > ) ON 1=1
> > INNER JOIN (
> > PUBLIC.TEST_GROUP_CLASS GC
> > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI1
> > ON GC.GCL_SID1 = GI1.GRP_SID
> > ) ON 1=1
> > INNER JOIN PUBLIC.TEST_GROUP_CLASS GC
> > ON 1=1
> > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI1
> > ON GC.GCL_SID1 = GI1.GRP_SID
> > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI2
> > ON GC.GCL_SID2 = GI2.GRP_SID
> > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI3
> > ON GC.GCL_SID3 = GI3.GRP_SID
> > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI4
> > ON GC.GCL_SID4 = GI4.GRP_SID
> > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI5
> > ON GC.GCL_SID5 = GI5.GRP_SID [42001-154]
> > at org.h2.message.DbException.getJdbcSQLException(DbException.java:
> > 327)
> > at org.h2.message.DbException.get(DbException.java:167)
> > at org.h2.message.DbException.getSyntaxError(DbException.java:192)
> > at org.h2.command.Parser.getSyntaxError(Parser.java:487)
> > at org.h2.command.Parser.read(Parser.java:2762)
> > at org.h2.command.Parser.readTableFilter(Parser.java:1033)
> > at org.h2.command.Parser.readTableFilter(Parser.java:1026)
> > at org.h2.command.Parser.readTableFilter(Parser.java:1026)
> > at org.h2.command.Parser.readTableFilter(Parser.java:1026)
> > at org.h2.command.Parser.parseSelectSimpleFromPart(Parser.java:1669)
> > at org.h2.command.Parser.parseSelectSimple(Parser.java:1776)
> > at org.h2.command.Parser.parseSelectSub(Parser.java:1663)
> > at org.h2.command.Parser.parseSelectUnion(Parser.java:1508)
> > at org.h2.command.Parser.parseSelect(Parser.java:1496)
> > at org.h2.command.Parser.parsePrepared(Parser.java:401)
> > at org.h2.command.Parser.parse(Parser.java:275)
> > at org.h2.command.Parser.parse(Parser.java:251)
> > at org.h2.command.Parser.prepare(Parser.java:201)
> > at org.h2.engine.Session.prepare(Session.java:399)
> > at org.h2.engine.Session.prepare(Session.java:386)
> > at org.h2.table.TableView.compileViewQuery(TableView.java:95)
> > at org.h2.table.TableView.initColumnsAndTables(TableView.java:139)
> > at org.h2.table.TableView.init(TableView.java:91)
> > at org.h2.table.TableView.<init>(TableView.java:59)
> > at org.h2.table.TableView.createTempView(TableView.java:416)
> > at org.h2.command.Parser.readTableFilter(Parser.java:1022)
> > at org.h2.command.Parser.parseSelectSimpleFromPart(Parser.java:1669)
> > at org.h2.command.Parser.parseSelectSimple(Parser.java:1776)
> > at org.h2.command.Parser.parseSelectSub(Parser.java:1663)
> > at org.h2.command.Parser.parseSelectUnion(Parser.java:1508)
> > at org.h2.command.Parser.parseSelect(Parser.java:1496)
> > at org.h2.command.Parser.parsePrepared(Parser.java:401)
> > at org.h2.command.Parser.parse(Parser.java:275)
> > at org.h2.command.Parser.parse(Parser.java:247)
> > at org.h2.command.Parser.prepare(Parser.java:201)
> > at org.h2.command.Parser.prepareCommand(Parser.java:214)
> > at org.h2.engine.Session.prepareLocal(Session.java:426)
> > at org.h2.engine.Session.prepareCommand(Session.java:374)
> > at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:
> > 1088)
> > at
> > org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:
> > 71)
> > at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:
> > 241)
> > at H2_13Test.main(H2_13Test.java:25)
>
> > On 5月12日, 午後5:30, Noel Grandin <[email protected]> wrote:
> >> what error message are you getting?
>
> >> HK wrote:
> >>> Hello,
> >>> I was using the h2 1.2, an update to 1.3.
> >>> 1.2 worked fine in SQL there.
> >>> But an error in 1.3.
> >>> create table TEST_GROUP_CLASS
> >>> (
> >>> GCL_SID1 integer not null,
> >>> GCL_SID2 integer not null,
> >>> GCL_SID3 integer not null,
> >>> GCL_SID4 integer not null,
> >>> GCL_SID5 integer not null,
> >>> primary key (GCL_SID1,
> >>> GCL_SID2,
> >>> GCL_SID3,
> >>> GCL_SID4,
> >>> GCL_SID5
> >>> )
> >>> ) ;
> >>> create table TEST_GROUPM
> >>> (
> >>> GRP_SID integer not null,
> >>> GRP_NAME varchar(20),
> >>> primary key (GRP_SID)
> >>> ) ;
> >>> This error occurs in SQL
> >>> select count(*) from
> >>> (
> >>> select
> >>> GC.GCL_SID1 as GCL_SID1,
> >>> GC.GCL_SID2 as GCL_SID2,
> >>> GC.GCL_SID3 as GCL_SID3,
> >>> GC.GCL_SID4 as GCL_SID4,
> >>> GC.GCL_SID5 as GCL_SID5,
> >>> GI1.GRP_NAME as GNAME1,
> >>> GI2.GRP_NAME as GNAME2,
> >>> GI3.GRP_NAME as GNAME3,
> >>> GI4.GRP_NAME as GNAME4,
> >>> GI5.GRP_NAME as GNAME5
> >>> from
> >>> (((((
> >>> TEST_GROUP_CLASS GC
> >>> left join TEST_GROUPM GI1 on GC.GCL_SID1 = GI1.GRP_SID)
> >>> left join
>
> ...
>
> もっと読む ≫
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
