Problems with SubQueries

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Problems with SubQueries

Chris Pratt
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: Problems with SubQueries

Chris Pratt
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 <[hidden email]> 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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: Problems with SubQueries

Fred Toussi-2
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(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 <[hidden email]> 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.
_______________________________________________
Hsqldb-user mailing list
 

------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user