I think the 3-argument TRANSLATE is only available if you enable the Oracle function table. Add '';fun=oracle" to your connect string.
I see a minor bug: the error messages should refer to TRANSLATE, not TRANSLATE3. On Wed, Jan 4, 2017 at 2:07 AM, Riccardo Tommasini <[email protected]> wrote: > Hello, > > I’m trying to implement the Replace function as described in this JIIRA [1]. > However, I keep facing the same exception: > > No match found for function signature REPLACE(<CHARACTER>, <CHARACTER>, > <CHARACTER>). > > Looking at the code, It turns out that this function is quite similar to > TRANSLATE3 function. > > This code<http://pastebin.com/jyvmEjiW> tries to execute the following query, > with the same exception as a results. Do you spot any problem? > > "SELECT TRANSLATE('ABBA','BB', 'CC') AS \”x\” FROM \"public\".\"tb_books\"" > > It follows the full body of the exception: > > Caused by: org.apache.calcite.runtime.CalciteContextException: From line 1, > column 8 to line 1, column 35: No match found for function signature > TRANSLATE3(<CHARACTER>, <CHARACTER>, <CHARACTER>) > at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) > at > sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) > at > sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) > at java.lang.reflect.Constructor.newInstance(Constructor.java:422) > at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:405) > at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:799) > at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:784) > at > org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:4075) > at > org.apache.calcite.sql.validate.SqlValidatorImpl.handleUnresolvedFunction(SqlValidatorImpl.java:1601) > at org.apache.calcite.sql.SqlFunction.deriveType(SqlFunction.java:278) > at org.apache.calcite.sql.SqlFunction.deriveType(SqlFunction.java:223) > at > org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:4440) > at > org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:4427) > at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:137) > at > org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1519) > at > org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:1502) > at org.apache.calcite.sql.SqlAsOperator.deriveType(SqlAsOperator.java:133) > at > org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:4440) > at > org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:4427) > at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:137) > at > org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1519) > at > org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:1502) > at > org.apache.calcite.sql.validate.SqlValidatorImpl.expandSelectItem(SqlValidatorImpl.java:428) > at > org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelectList(SqlValidatorImpl.java:3580) > at > org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3083) > at > org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60) > at > org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:86) > at > org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:896) > at > org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:877) > at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:208) > at > org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:852) > at > org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:563) > at > org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:551) > at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:229) > at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:193) > at > org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:734) > at > org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:597) > at > org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:567) > at > org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:215) > at > org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:594) > at > org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:613) > at > org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:139) > ... 7 more > Caused by: org.apache.calcite.sql.validate.SqlValidatorException: No match > found for function signature TRANSLATE3(<CHARACTER>, <CHARACTER>, <CHARACTER>) > at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) > at > sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) > at > sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) > at java.lang.reflect.Constructor.newInstance(Constructor.java:422) > at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:405) > at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:514) > ... 44 more > 11:01:07.488 [main] ERROR o.a.calcite.runtime.CalciteException - > org.apache.calcite.runtime.CalciteContextException: From line 1, column 8 to > line 1, column 35: No match found for function signature > TRANSLATE3(<CHARACTER>, <CHARACTER>, <CHARACTER>) > > Process finished with exit code 1 > > > > [1] https://issues.apache.org/jira/browse/CALCITE-420 > > > Riccardo Tommasini > Master Degree Computer Science > PhD Student at Politecnico di Milano (Italy) > streamreasoning.org<http://streamreasoning.org/> > > Submitted from an iPhone, I apologise for typos.
