Problems with SubQueries

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.<init>(Unknown Source)
at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source)
 at
org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:281)
at
org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:313)
 at
com.pearson.sre.sls.impl.dev.DevUniqueIdServiceImpl.lookupStudentIdExact(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. 
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