Nothing really,
Just felt that since straight through equality comparison did not work, perhaps casting to a number will produce the desired results. Unknown to us, some rows had that column set to XXX Thanks Again 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 @ 4:04:43 PM -----Original Message----- From: Charles Givre <cgi...@gmail.com> Sent: Wednesday, May 30, 2018 3:59 PM To: user@drill.apache.org Cc: Adesegun Alex Adeyemo <adesegun.adey...@interswitchgroup.com>; Adedamola Kolade <adedamola.kol...@interswitchgroup.com> Subject: Re: Error Joining Two Tables In Apache Drill 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.checkAndThrowExce >> p >> tion(Unknown >> Source) >> >> at >> com.mapr.drill.drill.dataengine.DRQryResultListener.getNextBatch(Unkn >> o >> wn >> Source) >> >> at >> com.mapr.drill.drill.dataengine.DRJDBCResultSet.doLoadRecordBatchData >> ( >> Unknown >> Source) >> >> at >> com.mapr.drill.drill.dataengine.DRJDBCResultSet.doMoveToNextRow(Unkno >> w >> 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(DelegatingStatem >> e >> nt.java:291) >> >> at >> org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatem >> e >> nt.java:291) >> >> at >> org.apache.zeppelin.jdbc.JDBCInterpreter.executeSql(JDBCInterpreter.j >> a >> va:581) >> >> at >> org.apache.zeppelin.jdbc.JDBCInterpreter.interpret(JDBCInterpreter.ja >> v >> a:692) >> >> at >> org.apache.zeppelin.interpreter.LazyOpenInterpreter.interpret(LazyOpe >> n >> Interpreter.java:97) >> >> at >> org.apache.zeppelin.interpreter.remote.RemoteInterpreterServer$Interp >> r >> etJob.jobRun(RemoteInterpreterServer.java:498) >> >> at org.apache.zeppelin.scheduler.Job.run(Job.java:175) >> >> at >> org.apache.zeppelin.scheduler.ParallelScheduler$JobRunner.run(Paralle >> l >> Scheduler.java:162) >> >> at >> java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:51 >> 1 >> ) >> >> 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* >> >> >>
