Hi Peter, Out of curiosity, what is the native data type of the product_code field? Is it really necessary to cast it to a character in the join statement? You might want to try the join w/o the data type conversion.
> On May 28, 2018, at 11:33, Peter Edike <[email protected]> > wrote: > > Hallos everyone > > I have the following query that attempts to join the result set of two views > on a common column > > select * > from dfs.vtucare.vw_tbl_transactions as table1 > join dfs.vtucare.vw_tbl_products as table2 > ON TO_CHAR(table1.product_code, '######') = TO_CHAR(table2.product_code, > '#####') limit 1 > > > On Running the Query, I get the following Error > > Error in expression at index -1. Error: Missing function implementation: > [castTINYINT(VARCHAR-OPTIONAL)]. Full expression: --UNKNOWN EXPRESSION--.. > Fragment 2:0 > [Error Id: 6c84fc61-c9d7-4d55-9ebd-bbbf7be84865 on > BGDTEST2.INTERSWITCH.COM:31010 <http://bgdtest2.interswitch.com:31010/>]. > at > com.mapr.drill.drill.dataengine.DRQryResultListener.checkAndThrowException(Unknown > Source) > at com.mapr.drill.drill.dataengine.DRQryResultListener.getNextBatch(Unknown > Source) > at > com.mapr.drill.drill.dataengine.DRJDBCResultSet.doLoadRecordBatchData(Unknown > Source) > at com.mapr.drill.drill.dataengine.DRJDBCResultSet.doMoveToNextRow(Unknown > Source) > at com.mapr.drill.drill.dataengine.DRJDBCQueryExecutor.execute(Unknown Source) > at com.mapr.drill.jdbc.common.SStatement.executeNoParams(Unknown Source) > at com.mapr.drill.jdbc.common.SStatement.execute(Unknown Source) > at > org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:291) > at > org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:291) > at > org.apache.zeppelin.jdbc.JDBCInterpreter.executeSql(JDBCInterpreter.java:581) > at > org.apache.zeppelin.jdbc.JDBCInterpreter.interpret(JDBCInterpreter.java:692) > at > org.apache.zeppelin.interpreter.LazyOpenInterpreter.interpret(LazyOpenInterpreter.java:97) > at > org.apache.zeppelin.interpreter.remote.RemoteInterpreterServer$InterpretJob.jobRun(RemoteInterpreterServer.java:498) > at org.apache.zeppelin.scheduler.Job.run(Job.java:175) > at > org.apache.zeppelin.scheduler.ParallelScheduler$JobRunner.run(ParallelScheduler.java:162) > at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) > at java.util.concurrent.FutureTask.run(FutureTask.java:266) > at > java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180) > at > java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293) > at > java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) > at > java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) > Caused by: com.mapr.drill.support.exceptions.GeneralException: > [MapR][DrillJDBCDriver](500165) Query execution error. Details: SYSTEM ERROR: > SchemaChangeException: Failure while trying to materialize incoming schema. > Errors: > Error in expression at index -1. Error: Missing function implementation: > [castTINYINT(VARCHAR-OPTIONAL)]. Full expression: --UNKNOWN EXPRESSION--.. > Fragment 2:0 > [Error Id: 6c84fc61-c9d7-4d55-9ebd-bbbf7be84865 on > BGDTEST2.INTERSWITCH.COM:31010 <http://bgdtest2.interswitch.com:31010/>]. > ... 21 more > > > > Please what am I doing wrong > > Kind Regards > > Peter Edike > Senior Software Engineer > Research and Development > Group Shared Technology > > Office NO: > Mobile NO: > Email: [email protected] > <mailto:[email protected]> > Interswitch Limited > 1648C Oko-Awo Street, Victoria Island Lagos > Customer Contact Centre 0700-9065000 > ΓΌ http://www.interswitchgroup.com <http://www.interswitchgroup.com/> > <https://www.quickteller.com/delight/> > This e-mail and all attachments transmitted with it remain the property of > Interswitch Limited , the information contained herein are private > confidential and intended solely for the use of the addressee. If you have > received this e-mail in error, kindly notify the sender. If you are not the > addressee, you should not disseminate, distribute or copy this e-mail. Kindly > notify Interswitch immediately by email if you have received this email in > error and delete this email and any attachment from your system Emails > cannot be guaranteed to be secure or error free as the message and any > attachments could be intercepted, corrupted, lost, delayed, incomplete or > amended. the contents of this email or its attachments have been scanned for > all viruses and all reasonable measures have been taken to ensure that no > viruses are present. Interswitch Limited and its subsidiaries do not accept > liability for damage caused by this email or any attachments.This message has > been marked as CONFIDENTIAL on Monday, May 28, 2018 @ 4:33:42 PM >
