Also, do you have some data that looks like 'XXX'? On Wed, May 30, 2018 at 1:18 PM Charles Givre <cgi...@gmail.com> wrote:
> 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 <peter.ed...@interswitchgroup.com> > 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: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 > >>> > >> > > > >
