What is wrong with my sql-statement?

[per.newgro]

Hi *,

i tried to execute my statement with jtds 1.2.2 But i get an
exception. With the mssql enterpise manager it works.
May a "guru" check this and push me to the right direction?
Maybe sub-selects are not possible with jtds?

Caused by: java.sql.SQLException: Falsche Syntax in der Nõhe des WHERE-
Schl³sselwortes. (Wrong syntax near Where)
at
net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:
368)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2816)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2254)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:631)
at
net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:
477)
at
net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:
777)
at
net.sourceforge.jtds.jdbcx.proxy.PreparedStatementProxy.executeQuery(PreparedStatementProxy.java:
56)
at
org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:
236)
at
my.backend.persistence.accessor.BookingAccessor.retrieveUsedRoomsInCategoryInPeriod(BookingAccessor.java:
510)
... 130 more


And here the statement:

sql.append("SELECT PRFUTURE.datum, BUCH.katnr, SUM(BUCH.anzahl) ");
sql.append("FROM (SELECT DISTINCT ");
sql.append("PRFUTURE.stationid, PRFUTURE.datum, BUCH.globdbis,
PRFUTURE.leistacc, PRFUTURE.anzerw, ");
sql.append("PRFUTURE.logis, PRFUTURE.fb, PRFUTURE.extras,
PRFUTURE.logisnt, PRFUTURE.fbnt, PRFUTURE.extrasnt, BUCH.buchnr, ");
sql.append("BUCH.anzahl, BUCH.reschar, BUCH.sharenr, PTYPGRP.typ,
BUCH.market, BUCH.katnr ");
sql.append("FROM proteluser.prfuture PRFUTURE LEFT OUTER JOIN ");
sql.append("proteluser.buch BUCH ON PRFUTURE.buchnr = BUCH.buchnr LEFT
OUTER JOIN ");
sql.append("proteluser.ptyp PTYP ON PRFUTURE.pdetail = PTYP.ptypnr
LEFT OUTER JOIN ");
sql.append("proteluser.ptypgrp PTYPGRP ON PTYP.grp = PTYPGRP.ptgnr
LEFT OUTER JOIN ");
sql.append("proteluser.kat KAT ON BUCH.katnr = KAT.katnr ");
sql.append("WHERE (PRFUTURE.stationid = ?) ");
sql.append(" AND (BUCH.reschar <= ?) ");
sql.append(" AND ((BUCH.sharenr < 1) OR (BUCH.sharenr = BUCH.buchnr))
");
sql.append(" AND (PRFUTURE.datum < BUCH.globdbis) ");
sql.append(" AND (KAT.zimmer = 1)) ");
sql.append("WHERE (PRFUTURE.datum >= ?) ");
sql.append(" AND (PRFUTURE.datum <= ?) ");
if (pCategory > 0) {
sql.append(" AND (BUCH.katnr = ?) ");
}
sql.append("GROUP BY PRFUTURE.datum, BUCH.katnr");

 

[Arne Vajhøj]

i tried to execute my statement with jtds 1.2.2 But i get an
exception. With the mssql enterpise manager it works.
May a "guru" check this and push me to the right direction?
Maybe sub-selects are not possible with jtds?

Caused by: java.sql.SQLException: Falsche Syntax in der Nõhe des WHERE-
Schl³sselwortes. (Wrong syntax near Where)
at

sql.append("SELECT PRFUTURE.datum, BUCH.katnr, SUM(BUCH.anzahl) ");
sql.append("FROM (SELECT DISTINCT ");
sql.append("PRFUTURE.stationid, PRFUTURE.datum, BUCH.globdbis,
PRFUTURE.leistacc, PRFUTURE.anzerw, ");
sql.append("PRFUTURE.logis, PRFUTURE.fb, PRFUTURE.extras,
PRFUTURE.logisnt, PRFUTURE.fbnt, PRFUTURE.extrasnt, BUCH.buchnr, ");
sql.append("BUCH.anzahl, BUCH.reschar, BUCH.sharenr, PTYPGRP.typ,
BUCH.market, BUCH.katnr ");
sql.append("FROM proteluser.prfuture PRFUTURE LEFT OUTER JOIN ");
sql.append("proteluser.buch BUCH ON PRFUTURE.buchnr = BUCH.buchnr LEFT
OUTER JOIN ");
sql.append("proteluser.ptyp PTYP ON PRFUTURE.pdetail = PTYP.ptypnr
LEFT OUTER JOIN ");
sql.append("proteluser.ptypgrp PTYPGRP ON PTYP.grp = PTYPGRP.ptgnr
LEFT OUTER JOIN ");
sql.append("proteluser.kat KAT ON BUCH.katnr = KAT.katnr ");
sql.append("WHERE (PRFUTURE.stationid = ?) ");
sql.append(" AND (BUCH.reschar <= ?) ");
sql.append(" AND ((BUCH.sharenr < 1) OR (BUCH.sharenr = BUCH.buchnr))
");
sql.append(" AND (PRFUTURE.datum < BUCH.globdbis) ");
sql.append(" AND (KAT.zimmer = 1)) ");

Try:

sql.append(" AND (KAT.zimmer = 1)) x ");

sql.append("WHERE (PRFUTURE.datum >= ?) ");
sql.append(" AND (PRFUTURE.datum <= ?) ");
if (pCategory > 0) {
sql.append(" AND (BUCH.katnr = ?) ");
}
sql.append("GROUP BY PRFUTURE.datum, BUCH.katnr");

Arne

 

[per.newgro]

Thanks Arne,

it works

Cheers
Per