Ok... So I tried with the following query and this time, I get the following exception
Query: select table2.product_code, table1.status from dfs.vtucare.tbl_transactions as table1 join dfs.vtucare.tbl_products as table2 on TO_NUMBER(table1.product_code, '######') = TO_NUMBER(table2.product_code, '#######') limit 1 Exception: Caused by: com.mapr.drill.support.exceptions.GeneralException: [MapR][DrillJDBCDriver](500165) Query execution error. Details: SYSTEM ERROR: UnsupportedOperationException: Cannot parse input: XXX with pattern : # However the following query returns results albeit in Exponent Notation SELECT TO_NUMBER(table1.product_code, '#########') from dfs.vtucare.vw_tbl_transactions as table1 LIMIT 1 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 @ 7:41:00 AM -----Original Message----- From: Charles Givre <cgi...@gmail.com> Sent: Monday, May 28, 2018 5:07 PM To: user@drill.apache.org Cc: Adedamola Kolade <adedamola.kol...@interswitchgroup.com> Subject: Re: Error Joining Two Tables In Apache Drill Ok… I believe the reason you are getting the errors is that Drill is quite sensitive to data types. The TO_CHAR function is used to convert numeric data into characters (VARCHAR). However, you are calling the TO_CHAR on data which is already a VARCHAR. Since there isn’t a function called TO_CHAR that accepts VARCHAR as input, you get the unhelpful error messages you received. I think the function you probably want to use in the JOIN statements is TO_NUMBER rather than TO_CHAR. (https://drill.apache.org/docs/data-type-conversion/#to_number <https://drill.apache.org/docs/data-type-conversion/#to_number>) I suspect the join will work if you substitute the TO_CHAR with TO_NUMBER. — C > On May 28, 2018, at 12:00, Peter Edike <peter.ed...@interswitchgroup.com> > wrote: > > I GET VARCHAR > > 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 Monday, May 28, 2018 @ > 5:00:55 PM > > -----Original Message----- > From: Charles Givre <cgi...@gmail.com> > Sent: Monday, May 28, 2018 4:59 PM > To: user@drill.apache.org > Cc: Adedamola Kolade <adedamola.kol...@interswitchgroup.com> > Subject: Re: Error Joining Two Tables In Apache Drill > > 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 <peter.ed...@interswitchgroup.com> >> 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: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 Monday, May 28, 2018 >> @ >> 4:54:04 PM >> >> -----Original Message----- >> From: Charles Givre <cgi...@gmail.com> >> Sent: Monday, May 28, 2018 4:50 PM >> To: user@drill.apache.org >> 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 <peter.ed...@interswitchgroup.com> >>> 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:peter.ed...@interswitchgroup.com >>> <mailto:peter.ed...@interswitchgroup.com> | 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 <cgi...@gmail.com <mailto:cgi...@gmail.com>> >>> Sent: Monday, May 28, 2018 4:40 PM >>> To: user@drill.apache.org <mailto:user@drill.apache.org> >>> 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 <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 >>>> <http://bgdtest2.interswitch.com:31010/><http://bgdtest2.interswitch.com:31010/ >>>> <http://bgdtest2.interswitch.com:31010/>>]. >>>> at >>>> com.mapr.drill.drill.dataengine.DRQryResultListener.checkAndThrowEx >>>> c >>>> e >>>> ption(Unknown Source) at >>>> com.mapr.drill.drill.dataengine.DRQryResultListener.getNextBatch(Un >>>> k >>>> n >>>> own Source) at >>>> com.mapr.drill.drill.dataengine.DRJDBCResultSet.doLoadRecordBatchDa >>>> t >>>> a >>>> (Unknown Source) at >>>> com.mapr.drill.drill.dataengine.DRJDBCResultSet.doMoveToNextRow(Unk >>>> n >>>> o >>>> 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(DelegatingStat >>>> e >>>> m >>>> ent.java:291) at >>>> org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStat >>>> e >>>> m >>>> ent.java:291) at >>>> org.apache.zeppelin.jdbc.JDBCInterpreter.executeSql(JDBCInterpreter. >>>> j >>>> ava:581) at >>>> org.apache.zeppelin.jdbc.JDBCInterpreter.interpret(JDBCInterpreter. >>>> j >>>> a >>>> va:692) at >>>> org.apache.zeppelin.interpreter.LazyOpenInterpreter.interpret(LazyO >>>> p >>>> e >>>> nInterpreter.java:97) at >>>> org.apache.zeppelin.interpreter.remote.RemoteInterpreterServer$Inte >>>> r >>>> p >>>> retJob.jobRun(RemoteInterpreterServer.java:498) >>>> at org.apache.zeppelin.scheduler.Job.run(Job.java:175) >>>> at >>>> org.apache.zeppelin.scheduler.ParallelScheduler$JobRunner.run(Paral >>>> l >>>> e >>>> lScheduler.java:162) at >>>> java.util.concurrent.Executors$RunnableAdapter.call(Executors.java: >>>> 5 >>>> 1 >>>> 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(ThreadPoolExecut >>>> o >>>> r >>>> .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: peter.ed...@interswitchgroup.com >>>> <mailto:peter.ed...@interswitchgroup.com> >>>> <mailto:peter.ed...@interswitchgroup.com >>>> <mailto: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/> >>>> <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 >> >
