HI Peter, Good to hear! What was in the data that was causing you to have to use those functions? —C
> On May 30, 2018, at 10: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* >> >> >>
