What do you get when you run: SELECT typeof(table1.product_code) from dfs.vtucare.vw_tbl_transactions as table1 LIMIT 1]
> On May 28, 2018, at 11:54, Peter Edike <[email protected]> > wrote: > > Same Exception occurs when I run the queries > > SELECT TO_CHAR(table1.product_code, '######') from > dfs.vtucare.vw_tbl_transactions as table1 LIMIT 10 > > SELECT TO_CHAR(table2.product_code, '#####’) FROM dfs.vtucare.vw_tbl_products > AS table2 LIMIT 10 > > > And yes, I changed the default storage plug in for the local filesystem > > > > Best regards, > Peter Edike > > Senior Software Engineer > Interswitch > > Tel. | Mobile. | IP Phone. > Fax. | mailto:[email protected] | 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 Monday, May 28, 2018 @ > 4:54:04 PM > > -----Original Message----- > From: Charles Givre <[email protected]> > Sent: Monday, May 28, 2018 4:50 PM > To: [email protected] > Subject: Re: Error Joining Two Tables In Apache Drill > > Hi Peter, > In that case, I’m a little confused. In your query, you have dfs as the > storage plugin which is the default storage plugin for the local file system. > Did you change that? > > What happens if you execute the following queries: > SELECT TO_CHAR(table1.product_code, '######') from > dfs.vtucare.vw_tbl_transactions as table1 LIMIT 10 > > SELECT TO_CHAR(table2.product_code, '#####’) FROM dfs.vtucare.vw_tbl_products > AS table2 LIMIT 10 > > Do those queries produce results? > —C > > >> On May 28, 2018, at 11:43, Peter Edike <[email protected]> >> wrote: >> >> The query queries a no-sql database....so I don’t think there is type >> information. However, Querying without the cast returns an empty >> result set, but I can see matching rows when I query the two views >> independently >> >> Best regards, >> Peter Edike >> >> Senior Software Engineer >> Interswitch >> >> Tel. | Mobile. | IP Phone. >> Fax. | mailto:[email protected] >> <mailto:[email protected]> | http:// >> >> http://www.interswitchgroup.com <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 Monday, May 28, 2018 @ >> 4:43:53 PM >> >> -----Original Message----- >> From: Charles Givre <[email protected] <mailto:[email protected]>> >> Sent: Monday, May 28, 2018 4:40 PM >> To: [email protected] <mailto:[email protected]> >> Subject: Re: Error Joining Two Tables In Apache Drill >> >> 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/><http://bgdtest2.interswitch.com:31010/ >>> <http://bgdtest2.interswitch.com:31010/>>]. >>> at >>> com.mapr.drill.drill.dataengine.DRQryResultListener.checkAndThrowExce >>> ption(Unknown Source) at >>> com.mapr.drill.drill.dataengine.DRQryResultListener.getNextBatch(Unkn >>> own Source) at >>> com.mapr.drill.drill.dataengine.DRJDBCResultSet.doLoadRecordBatchData >>> (Unknown Source) at >>> com.mapr.drill.drill.dataengine.DRJDBCResultSet.doMoveToNextRow(Unkno >>> wn 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 >>> ent.java:291) at >>> org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatem >>> ent.java:291) at >>> org.apache.zeppelin.jdbc.JDBCInterpreter.executeSql(JDBCInterpreter.j >>> ava:581) at >>> org.apache.zeppelin.jdbc.JDBCInterpreter.interpret(JDBCInterpreter.ja >>> va:692) at >>> org.apache.zeppelin.interpreter.LazyOpenInterpreter.interpret(LazyOpe >>> nInterpreter.java:97) at >>> org.apache.zeppelin.interpreter.remote.RemoteInterpreterServer$Interp >>> retJob.jobRun(RemoteInterpreterServer.java:498) >>> at org.apache.zeppelin.scheduler.Job.run(Job.java:175) >>> at >>> org.apache.zeppelin.scheduler.ParallelScheduler$JobRunner.run(Paralle >>> lScheduler.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. >>> 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/><http://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]> >>> <mailto:[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/> <http://www.interswitchgroup.com/ >>> <http://www.interswitchgroup.com/>> >>> <https://www.quickteller.com/delight/ >>> <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 >
