you can use TRIM instead of LTRIM and the RTRIM Thanks, Divya
On 30 May 2018 at 22:57, Peter Edike <peter.ed...@interswitchgroup.com> wrote: > Hi, > > I figured out the problem why results where not being returned > > select * > from dfs.vtucare.tbl_transactions as table1 join dfs.vtucare.tbl_products > as table2 > on LTRIM(RTRIM(table1.product_code)) = LTRIM(RTRIM(table2.product_code)) > limit 1 > > > A LTRIM and RTRIM on the join columns without any casting or conversion, > solved the problem > > Best regards, > Peter Edike > > Senior Software Engineer > Interswitch > > Tel. | Mobile. | IP Phone. > Fax. | mailto:peter.ed...@interswitchgroup.com | http:// > > http://www.interswitchgroup.com > > InterswitchThis 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 Wednesday, May 30, 2018 @ > 3:57:36 PM > > -----Original Message----- > From: Divya Gehlot <divya.htco...@gmail.com> > Sent: Wednesday, May 30, 2018 3:44 PM > To: user@drill.apache.org > Subject: Re: Error Joining Two Tables In Apache Drill > > What is the data type of the joining data column views ? > Are both columns in views have same data types ? > > Thanks , > Divya > > On Mon, 28 May 2018 at 11:33 PM, Peter Edike < > peter.ed...@interswitchgroup.com> 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]. > > > > at > > com.mapr.drill.drill.dataengine.DRQryResultListener.checkAndThrowExcep > > tion(Unknown > > Source) > > > > at > > com.mapr.drill.drill.dataengine.DRQryResultListener.getNextBatch(Unkno > > wn > > Source) > > > > at > > com.mapr.drill.drill.dataengine.DRJDBCResultSet.doLoadRecordBatchData( > > Unknown > > Source) > > > > at > > com.mapr.drill.drill.dataengine.DRJDBCResultSet.doMoveToNextRow(Unknow > > n > > 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(DelegatingStateme > > nt.java:291) > > > > at > > org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStateme > > nt.java:291) > > > > at > > org.apache.zeppelin.jdbc.JDBCInterpreter.executeSql(JDBCInterpreter.ja > > va:581) > > > > at > > org.apache.zeppelin.jdbc.JDBCInterpreter.interpret(JDBCInterpreter.jav > > a:692) > > > > at > > org.apache.zeppelin.interpreter.LazyOpenInterpreter.interpret(LazyOpen > > Interpreter.java:97) > > > > at > > org.apache.zeppelin.interpreter.remote.RemoteInterpreterServer$Interpr > > etJob.jobRun(RemoteInterpreterServer.java:498) > > > > at org.apache.zeppelin.scheduler.Job.run(Job.java:175) > > > > at > > org.apache.zeppelin.scheduler.ParallelScheduler$JobRunner.run(Parallel > > Scheduler.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.a > > ccess$201(ScheduledThreadPoolExecutor.java:180) > > > > at > > java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.r > > un(ScheduledThreadPoolExecutor.java:293) > > > > at > > java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.j > > ava: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]. > > > > ... 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:* peter.ed...@interswitchgroup.com 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* > > > > > > >
