java - UCanAccess "object not found" error for query that references its own column alias -

since jdbc:odbc bridge in not longer supported java 8 try replace ucanaccess v3.0, ii facing issue, see following.

this code use in order connect database :

string url = "jdbc:ucanaccess://d:/adel/adel local/adel_data.accdb";         try         {             //class.forname("sun.jdbc.odbc.jdbcodbcdriver");             class.forname("net.ucanaccess.jdbc.ucanaccessdriver");          }         catch (java.lang.classnotfoundexception e)         {             system.err.print("classnotfoundexception: ");             system.err.println(e.getmessage());             errorfile.writeerror(thread.currentthread().getstacktrace()[2].getlinenumber(), e);         } 

to run query same code using jdbc:odbc

connection c = connectdb.doconnect();  string selectstring = "select distinct [maintenance input check due list].[interval (mos)], [maintenance input check due list].inputaircrafthours, [maintenance input check due list].inputaircraftlandings,  [maintenance input check due list].check, [maintenance input check due list].title, [maintenance input check due list].inputnumber, [maintenance input check due list].aircraftcurrenttsn, [maintenance input check due list].aircraftcurrentcsn, [maintenance input check due list].aircraft, [maintenance input check due list].inputdateout, [maintenance input check due list].[interval (hrs)], [maintenance input check due list].[interval(ldgs)], [maintenance input check due list].[nextduecheckmonth], [maintenance input check due list].nextduecheckhours, [maintenance input check due list].nextduecheckldgs, [maintenance input check due list].remaincheckmonth, [maintenance input check due list].remaincheckhours,[maintenance input check due list].remaincheckldgs,[maintenance input check due list].inputrepairstation,[maintenance input check due list].inputdescription "             + "from [maintenance input table] right join ([maintenance schedule check] right join   [...]  try     {         stmt = c.createstatement();         resultset data = stmt.executequery(selectstring);         system.out.print("(aircraftduelist-ln539) " + "requete passed\n");         while (data.next())         {[...]} } catch (sqlexception e)     {         errorfile.writeerror(thread.currentthread().getstacktrace()[2].getlinenumber(), e);         system.err.println("sqlexception: "                 + e.getmessage());         e.printstacktrace();     } 

but query throw exception :

 sqlexception: ucaexc:::3.0.0 user lacks privilege or object not found: nextduecheckmonth net.ucanaccess.jdbc.ucanaccesssqlexception: ucaexc:::3.0.0 user lacks privilege or object not found: nextduecheckmonth @     net.ucanaccess.jdbc.ucanaccessstatement.executequery(ucanaccessstatement.java:211) @ main.aircraftduelist.insppack(aircraftduelist.java:626) @ main.aircraftduelist.createpdf(aircraftduelist.java:88) @ main.aircraftlist.<init>(aircraftlist.java:166) @ launch.main(launch.java:13) caused by: java.sql.sqlsyntaxerrorexception: user lacks privilege or object not found: nextduecheckmonth @ org.hsqldb.jdbc.jdbcutil.sqlexception(unknown source) @ org.hsqldb.jdbc.jdbcutil.sqlexception(unknown source) @ org.hsqldb.jdbc.jdbcstatement.fetchresult(unknown source) @ org.hsqldb.jdbc.jdbcstatement.executequery(unknown source) @ net.ucanaccess.jdbc.ucanaccessstatement.executequery(ucanaccessstatement.java:208) ... 4 more caused by: org.hsqldb.hsqlexception: user lacks privilege or object not found: nextduecheckmonth @ org.hsqldb.error.error.error(unknown source) @ org.hsqldb.error.error.error(unknown source) @ org.hsqldb.expressioncolumn.checkcolumnsresolved(unknown source) @ org.hsqldb.rangevariable.resolverangetable(unknown source) @ org.hsqldb.queryspecification.resolverangevariables(unknown source) @ org.hsqldb.queryspecification.resolvereferences(unknown source) @ org.hsqldb.parserdql.xreadjoinedtableassubqueryornull(unknown source) @ org.hsqldb.parserdql.readtableorsubquery(unknown source) @ org.hsqldb.parserdql.xreadtablereference(unknown source) @ org.hsqldb.parserdql.xreadfromclause(unknown source) @ org.hsqldb.parserdql.xreadtableexpression(unknown source) @ org.hsqldb.parserdql.xreadqueryspecification(unknown source) @ org.hsqldb.parserdql.xreadsimpletable(unknown source) @ org.hsqldb.parserdql.xreadqueryprimary(unknown source) @ org.hsqldb.parserdql.xreadqueryterm(unknown source) @ org.hsqldb.parserdql.xreadqueryexpressionbody(unknown source) @ org.hsqldb.parserdql.xreadqueryexpression(unknown source) @ org.hsqldb.parserdql.compilecursorspecification(unknown source) @ org.hsqldb.parsercommand.compilepart(unknown source) @ org.hsqldb.parsercommand.compilestatements(unknown source) @ org.hsqldb.session.executedirectstatement(unknown source) @ org.hsqldb.session.execute(unknown source) ... 7 more 

this query call tables , stored query in access, "nextduecheckmonth" alias field stored query in access named "[maintenance input check due list]".

in stored query [maintenance input check due list] field nextduecheckmonth construct :

iif([interval (mos)] null,null,dateadd("m",[interval (mos)],[inputdateout])) nextduecheckmonth 

someone have facing kind of issue ?

ps : query's run in access

as mentioned in comment other answer, problem query had clause trying reference column alias defined in same query. doesn't work in ucanaccess, e.g.,

sql =          "select date1 datealias " +         "from datetest " +         "where datealias not null"; 

throws

net.ucanaccess.jdbc.ucanaccesssqlexception: ucaexc:::3.0.0 user lacks privilege or object not found: datealias

but not work in access since access not recognize alias column name , therefore prompts parameter value:

one workaround issue encapsulate original query subquery outer query can use alias:

sql =          "select datealias " +         "from (select date1 datealias datetest) " +         "where datealias not null";