Re: Problems with SubQueries

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(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)




On Tue, Aug 6, 2013 at 11:16 AM, Chris Pratt <[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(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