Encountered "placeholder_asterix"

Ray · December 14, 2024, 11:56am

When executing a query of query using Lucee, I am getting the below error. The cfml and query work in Adobe Coldfusion.

Here is the query of MS SQL Server:

Select Get_TaxRates.Rate_Id,
Get_TaxRates.Rate_C_Rate,
Rate_B_Bill$Display,
Sum(BillingTable.Service_Amount) AS Taxed_Amount,
(Get_TaxRates.Rate_C_Rate*Sum(BillingTable.Service_Amount)) As Tax_Charge
From Get_TaxRates, BillingTable
Where Get_TaxRates.Rate_Rate_Taxing_Id=BillingTable.Rate_Id
Group By Get_TaxRates.Rate_Id,
Get_TaxRates.Rate_C_Rate,
Rate_B_Bill$Display

Here is the error:
Lucee 5.3.8.206 Error (database)
Message Encountered “placeholder_asterix” at line 5, column 75.
Was expecting one of:
“AND” …
“BETWEEN” …
“IN” …
“IS” …
“LIKE” …
“NOT” …
“OR” …
“)” …
“=” …
“.” …
“!=” …
“#” …
“<>” …
“>” …
“>=” …
“<” …
“<=” …
“+” …
“-” …
"" …
“||” …
“/” …
“**” …
“(” …
SQL Select Get_TaxRates.Rate_Id,
Get_TaxRates.Rate_C_Rate,
Rate_B_Bill$Display,
Sum(BillingTable.Service_Amount) AS Taxed_Amount,
(Get_TaxRates.Rate_C_RateSum(BillingTable.Service_Amount)) As Tax_Charge
From Get_TaxRates, BillingTable
Where Get_TaxRates.Rate_Rate_Taxing_Id=BillingTable.Rate_Id
Group By Get_TaxRates.Rate_Id,
Get_TaxRates.Rate_C_Rate,
Rate_B_Bill$Display
Stacktrace The Error Occurred in
/Users/webbill/tomcat/webapps/ROOT/SQL_Master/templates/Billing_Calculate.cfc: line 357

355: Get_TaxRates.Rate_C_Rate,
356: Rate_B_Bill$Display
357: </cfquery>
358: <cfelse>
359: <cfquery datasource="#session.main_datasource#" name="Get_Taxes">

called from /Users/webbill/tomcat/webapps/ROOT/SQL_Master/Billing_Resident_Estimate_Calculate.cfm: line 166
Java Stacktrace lucee.runtime.exp.DatabaseException: Encountered “placeholder_asterix” at line 5, column 75.
Was expecting one of:
“AND” …
“BETWEEN” …
“IN” …
“IS” …
“LIKE” …
“NOT” …
“OR” …
“)” …
“=” …
“.” …
“!=” …
“#” …
“<>” …
“>” …
“>=” …
“<” …
“<=” …
“+” …
“-” …
“*” …
“||” …
“/” …
“**” …
“(” …

at lucee.runtime.db.HSQLDBHandler.execute(HSQLDBHandler.java:311)
at lucee.runtime.tag.Query.executeQoQ(Query.java:1110)
at lucee.runtime.tag.Query._doEndTag(Query.java:680)
at lucee.runtime.tag.Query.doEndTag(Query.java:565)
at sql_master.templates.billing_calculate_cfc$cf$5t.udfCall(/SQL_Master/templates/Billing_Calculate.cfc:357)
at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:106)
at lucee.runtime.type.UDFImpl._call(UDFImpl.java:344)
at lucee.runtime.type.UDFImpl.callWithNamedValues(UDFImpl.java:207)
at lucee.runtime.ComponentImpl._call(ComponentImpl.java:651)
at lucee.runtime.ComponentImpl._call(ComponentImpl.java:572)
at lucee.runtime.ComponentImpl.callWithNamedValues(ComponentImpl.java:1925)
at lucee.runtime.tag.Invoke.doComponent(Invoke.java:209)
at lucee.runtime.tag.Invoke.doEndTag(Invoke.java:186)
at sql_master.billing_resident_estimate_calculate_cfm$cf$5s.call(/SQL_Master/Billing_Resident_Estimate_Calculate.cfm:166)
at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:1003)
at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:926)
at lucee.runtime.listener.ClassicAppListener._onRequest(ClassicAppListener.java:65)
at lucee.runtime.listener.MixedAppListener.onRequest(MixedAppListener.java:45)
at lucee.runtime.PageContextImpl.execute(PageContextImpl.java:2460)
at lucee.runtime.PageContextImpl._execute(PageContextImpl.java:2450)
at lucee.runtime.PageContextImpl.executeCFML(PageContextImpl.java:2421)
at lucee.runtime.engine.Request.exe(Request.java:45)
at lucee.runtime.engine.CFMLEngineImpl._service(CFMLEngineImpl.java:1179)
at lucee.runtime.engine.CFMLEngineImpl.serviceCFML(CFMLEngineImpl.java:1125)
at lucee.loader.engine.CFMLEngineWrapper.serviceCFML(CFMLEngineWrapper.java:97)
at lucee.loader.servlet.CFMLServlet.service(CFMLServlet.java:51)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:764)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:196)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:542)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:698)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:364)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:624)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:831)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1673)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.base/java.lang.Thread.run(Thread.java:833)

Timestamp 12/13/24 6:39:35 PM EST

andreas · December 14, 2024, 3:42pm

Is that a $ sign in your SELECT statement ($Display)? Looks like some fancy SQL. Don’t think that can be compatible with QoQ.

Ray · December 14, 2024, 4:35pm

Yes, that is a $. I use that in many places as a field name in SQL Server without any issue. But I took it out of this query and still got the same error.
Thanks for the idea

Gavin_Baumanis · December 16, 2024, 5:32am

Excuse my very obvious question… did you remove it both locations?

Ray · December 16, 2024, 12:59pm

I remove both the $ in both places by renaming the column in SQL . It affected other cfm, so I changed all those and got a new error below.
Thanks for any help.

mbuckman · December 18, 2024, 7:03pm

Ray, what was the new error that you received? I don’t see it.

Ray · December 18, 2024, 8:42pm

Version:1.0 StartHTML:000000096 EndHTML:000010964 StartFragment:000000096 EndFragment:000010964
Lucee 5.3.8.206 Error (database)
Message Not in aggregate function or group by clause: org.hsqldb.Expression@28caccf0 in statement [Select Get_TaxRates.Rate_Id,
Get_TaxRates.Rate_C_Rate,
Rate_B_BillDisplay,
Sum(BillingTable.Service_Amount) AS Taxed_Amount,
(Get_TaxRates.Rate_C_RateSum(BillingTable.Service_Amount)) As Tax_Charge
From Get_TaxRates, BillingTable
Where Get_TaxRates.Rate_Rate_Taxing_Id=BillingTable.Rate_Id
Group By Get_TaxRates.Rate_Id,
Get_TaxRates.Rate_C_Rate,
Rate_B_BillDisplay]
SQL Select Get_TaxRates.Rate_Id,
Get_TaxRates.Rate_C_Rate,
Rate_B_BillDisplay,
Sum(BillingTable.Service_Amount) AS Taxed_Amount,
(Get_TaxRates.Rate_C_RateSum(BillingTable.Service_Amount)) As Tax_Charge
From Get_TaxRates, BillingTable
Where Get_TaxRates.Rate_Rate_Taxing_Id=BillingTable.Rate_Id
Group By Get_TaxRates.Rate_Id,
Get_TaxRates.Rate_C_Rate,
Rate_B_BillDisplay
DatabaseName HSQL Database Engine
DatabaseVersion 1.8.0
DriverName HSQL Database Engine Driver
DriverVersion 1.8.0
Datasource _queryofquerydb
Stacktrace The Error Occurred in
/Users/webbill/tomcat/webapps/ROOT/SQL_Master/templates/Billing_Calculate.cfc: line 357
355: Get_TaxRates.Rate_C_Rate,
356: Rate_B_BillDisplay
357:
358:
359:

called from /Users/webbill/tomcat/webapps/ROOT/SQL_Master/Billing_Resident_Estimate_Calculate.cfm: line 166
Java Stacktrace lucee.runtime.exp.DatabaseException: Not in aggregate function or group by clause: org.hsqldb.Expression@28caccf0 in statement [Select Get_TaxRates.Rate_Id,
Get_TaxRates.Rate_C_Rate,
Rate_B_BillDisplay,
Sum(BillingTable.Service_Amount) AS Taxed_Amount,
(Get_TaxRates.Rate_C_Rate*Sum(BillingTable.Service_Amount)) As Tax_Charge
From Get_TaxRates, BillingTable
Where Get_TaxRates.Rate_Rate_Taxing_Id=BillingTable.Rate_Id
Group By Get_TaxRates.Rate_Id,
Get_TaxRates.Rate_C_Rate,
Rate_B_BillDisplay]
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.jdbcStatement.fetchResult(Unknown Source)
at org.hsqldb.jdbc.jdbcStatement.execute(Unknown Source)
at lucee.runtime.type.util.QueryUtil.execute(QueryUtil.java:320)
at lucee.runtime.type.QueryImpl.execute(QueryImpl.java:287)
at lucee.runtime.type.QueryImpl.(QueryImpl.java:235)
at lucee.runtime.db.HSQLDBHandler.__execute(HSQLDBHandler.java:358)
at lucee.runtime.db.HSQLDBHandler._execute(HSQLDBHandler.java:319)
at lucee.runtime.db.HSQLDBHandler.execute(HSQLDBHandler.java:307)
at lucee.runtime.tag.Query.executeQoQ(Query.java:1110)
at lucee.runtime.tag.Query._doEndTag(Query.java:680)
at lucee.runtime.tag.Query.doEndTag(Query.java:565)
at sql_master.templates.billing_calculate_cfc$cf$9n.udfCall(/SQL_Master/templates/Billing_Calculate.cfc:357)
at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:106)
at lucee.runtime.type.UDFImpl._call(UDFImpl.java:344)
at lucee.runtime.type.UDFImpl.callWithNamedValues(UDFImpl.java:207)
at lucee.runtime.ComponentImpl._call(ComponentImpl.java:651)
at lucee.runtime.ComponentImpl._call(ComponentImpl.java:572)
at lucee.runtime.ComponentImpl.callWithNamedValues(ComponentImpl.java:1925)
at lucee.runtime.tag.Invoke.doComponent(Invoke.java:209)
at lucee.runtime.tag.Invoke.doEndTag(Invoke.java:186)
at sql_master.billing_resident_estimate_calculate_cfm$cf$9m.call(/SQL_Master/Billing_Resident_Estimate_Calculate.cfm:166)
at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:1003)
at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:926)
at lucee.runtime.listener.ClassicAppListener._onRequest(ClassicAppListener.java:65)
at lucee.runtime.listener.MixedAppListener.onRequest(MixedAppListener.java:45)
at lucee.runtime.PageContextImpl.execute(PageContextImpl.java:2460)
at lucee.runtime.PageContextImpl._execute(PageContextImpl.java:2450)
at lucee.runtime.PageContextImpl.executeCFML(PageContextImpl.java:2421)
at lucee.runtime.engine.Request.exe(Request.java:45)
at lucee.runtime.engine.CFMLEngineImpl._service(CFMLEngineImpl.java:1179)
at lucee.runtime.engine.CFMLEngineImpl.serviceCFML(CFMLEngineImpl.java:1125)
at lucee.loader.engine.CFMLEngineWrapper.serviceCFML(CFMLEngineWrapper.java:97)
at lucee.loader.servlet.CFMLServlet.service(CFMLServlet.java:51)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:764)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:196)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:542)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:698)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:364)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:624)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:831)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1673)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.base/java.lang.Thread.run(Thread.java:833)

Timestamp 12/16/24 7:38:33 AM EST

mbuckman · December 18, 2024, 8:56pm

One suggestion for you is to try to trim down this query, and then bit-by-bit add chunks back into the query until you nail down where the error is. Do you have an RMDBS for your HSQLDB? That may help you troubleshoot the query apart from Lucee.