Re: Problems with SubQueries

Correct. PARTITION is not supported while ROW_NUMBER() OVER () is
supported. You need to rewrite using the available features.

Fred

On Tue, Aug 6, 2013, at 20:25, Chris Pratt wrote:

After some work, I seem to have been able to move on to a new error.
It appears that the PARTITION keyword is not supported by HSQLDB?  Is
there an alternative solution?
  (*Chris*)

Query:
SELECT s.STATE_STUDENTNUMBER, s.SSN, sc.NAME AS SCHOOL_NAME,
       (SELECT VALUE FROM PREFS WHERE NAME='districtname') AS
DISTRICT_NAME,
       (SELECT VALUE FROM PREFS WHERE NAME='districtnumber') AS
DISTRICT_ID,
       s.FIRST_NAME, s.MIDDLE_NAME, s.LAST_NAME, s.GENDER, s.DOB,
s.GRADE_LEVEL,
       s.STUDENT_NUMBER, s.DCID, s.ENTRYDATE, NVL(s.EXITDATE,SYSDATE)
AS EXITDATE,
       ROW_NUMBER() OVER (PARTITION BY s.STATE_STUDENTNUMBER ORDER BY
s.STATE_STUDENTNUMBER, s.ENTRYDATE DESC, EXITDATE DESC, s.DCID DESC) AS
row_num
  FROM STUDENTS s
    LEFT JOIN SCHOOLS sc ON (sc.SCHOOL_NUMBER = s.SCHOOLID)
  WHERE s.STATE_STUDENTNUMBER=? ORDER BY s.STUDENT_NUMBER, row_num;

Exception:
java.sql.SQLSyntaxErrorException: unexpected token: PARTITION required:
)
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unknown Source)
at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source)
at
org.apache.commons.dbcp.DelegatingConnection.prepareStatement(Delegatin
gConnection.java:281)
at
org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.pr
epareStatement(PoolingDataSource.java:313)
at
com.pearson.sre.sls.impl.dev.DevUniqueIdServiceImpl.lookupStudentIdExac
t(DevUniqueIdServiceImpl.java:200)


On Tue, Aug 6, 2013 at 11:16 AM, Chris Pratt
<[1][email protected]> wrote:

I am using the following complex query in our production code.  It
works fine against Oracle, but I'm trying to use HSQLDB to unit test
the code and it's throwing exceptions.  Is there a way to write this
statement that would be acceptable to both Oracle and HSQLDB?  If not,
is there at least a way to write it that would be acceptable to HSQLDB?
 Thanks.
  (*Chris*)

SELECT s.STATE_STUDENTNUMBER, s.SSN, sc.NAME AS SCHOOL_NAME,
       (SELECT VALUE FROM PREFS WHERE NAME='districtname') AS
DISTRICT_NAME,
       (SELECT VALUE FROM PREFS WHERE NAME='districtnumber') AS
DISTRICT_ID,
       s.FIRST_NAME, s.MIDDLE_NAME, s.LAST_NAME, s.GENDER, s.DOB,
s.GRADE_LEVEL,
       s.STUDENT_NUMBER, s.DCID, s.ENTRYDATE, NVL(s.EXITDATE,SYSDATE)
AS EXITDATE,
       ROW_NUMBER() OVER (PARTITION BY s.STATE_STUDENTNUMBER ORDER BY
s.STATE_STUDENTNUMBER, s.ENTRYDATE DESC, EXITDATE DESC, s.DCID DESC) AS
row_num
  FROM STUDENTS s
    LEFT JOIN SCHOOLS sc ON (sc.SCHOOL_NUMBER = s.SCHOOLID)
  WHERE s.STATE_STUDENTNUMBER=? ORDER BY s.STUDENT_NUMBER, row_num;

Under HSQLDB, I'm getting the following exception:

java.sql.SQLSyntaxErrorException: unexpected token: SELECT
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.&lt;init&gt;(Unknown Source)
at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source)
at
org.apache.commons.dbcp.DelegatingConnection.prepareStatement(Delegatin
gConnection.java:281)
at
org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.pr
epareStatement(PoolingDataSource.java:313)
at
com.pearson.sre.sls.impl.dev.DevUniqueIdServiceImpl.lookupStudentIdExac
t(DevUniqueIdServiceImpl.java:200)

-----------------------------------------------------------------------
-------

Get 100% visibility into Java/.NET code with AppDynamics Lite!

It's a free troubleshooting tool designed for production.

Get down to code-level detail for bottlenecks, with <2% overhead.

Download for free and get started troubleshooting in minutes.

[2]http://pubads.g.doubleclick.net/gampad/clk?id=48897031&iu=/4140/ostg
.clktrk

_______________________________________________

Hsqldb-user mailing list

[3][email protected]

[4]https://lists.sourceforge.net/lists/listinfo/hsqldb-user

References

1. mailto:[email protected]
2. http://pubads.g.doubleclick.net/gampad/clk?id=48897031&iu=/4140/ostg.clktrk
3. mailto:[email protected]
4. https://lists.sourceforge.net/lists/listinfo/hsqldb-user

------------------------------------------------------------------------------
Get 100% visibility into Java/.NET code with AppDynamics Lite!
It's a free troubleshooting tool designed for production.
Get down to code-level detail for bottlenecks, with <2% overhead. 
Download for free and get started troubleshooting in minutes. 
http://pubads.g.doubleclick.net/gampad/clk?id=48897031&iu=/4140/ostg.clktrk

_______________________________________________
Hsqldb-user mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user