Three questions… 1. Have you tried this with the format string ‘#’ (A single #) 2. Have you tried the join w/o any function wrapper around the field? 3. I notice that the number of # is different for both fields. Is that deliberate?
— C > On May 30, 2018, at 02:41, Peter Edike <[email protected]> > wrote: > > 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:[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 Wednesday, May 30, 2018 @ > 7:41:00 AM > > -----Original Message----- > From: Charles Givre <[email protected]> > Sent: Monday, May 28, 2018 5:07 PM > To: [email protected] > Cc: Adedamola Kolade <[email protected]> > 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 <[email protected]> >> wrote: >> >> I GET VARCHAR >> >> 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 @ >> 5:00:55 PM >> >> -----Original Message----- >> From: Charles Givre <[email protected]> >> Sent: Monday, May 28, 2018 4:59 PM >> To: [email protected] >> Cc: Adedamola Kolade <[email protected]> >> 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 <[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.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: [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 >>> >> >
