You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@drill.apache.org by Peter Edike <pe...@interswitchgroup.com> on 2018/05/28 15:33:43 UTC
Error Joining Two Tables In Apache Drill
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.checkAndThrowException(Unknown Source)
at com.mapr.drill.drill.dataengine.DRQryResultListener.getNextBatch(Unknown Source)
at com.mapr.drill.drill.dataengine.DRJDBCResultSet.doLoadRecordBatchData(Unknown Source)
at com.mapr.drill.drill.dataengine.DRJDBCResultSet.doMoveToNextRow(Unknown 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(DelegatingStatement.java:291)
at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:291)
at org.apache.zeppelin.jdbc.JDBCInterpreter.executeSql(JDBCInterpreter.java:581)
at org.apache.zeppelin.jdbc.JDBCInterpreter.interpret(JDBCInterpreter.java:692)
at org.apache.zeppelin.interpreter.LazyOpenInterpreter.interpret(LazyOpenInterpreter.java:97)
at org.apache.zeppelin.interpreter.remote.RemoteInterpreterServer$InterpretJob.jobRun(RemoteInterpreterServer.java:498)
at org.apache.zeppelin.scheduler.Job.run(Job.java:175)
at org.apache.zeppelin.scheduler.ParallelScheduler$JobRunner.run(ParallelScheduler.java:162)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
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].
... 21 more
Please what am I doing wrong
Kind Regards
________________________________
Peter Edike
Senior Software Engineer
Research and Development
Group Shared Technology
[cid:image002.png@01D3F6A1.A3FDC080]
Office NO:
Mobile NO:
Email: peter.edike@interswitchgroup.com<ma...@interswitchgroup.com>
Interswitch Limited
1648C Oko-Awo Street, Victoria Island Lagos
Customer Contact Centre 0700-9065000
? http://www.interswitchgroup.com<http://www.interswitchgroup.com/>
[cid:image003.png@01D3F6A1.A3FDC080]<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
RE: Error Joining Two Tables In Apache Drill
Posted by Peter Edike <pe...@interswitchgroup.com>.
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.edike@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 <cg...@gmail.com>
Sent: Wednesday, May 30, 2018 3:59 PM
To: user@drill.apache.org
Cc: Adesegun Alex Adeyemo <ad...@interswitchgroup.com>; Adedamola Kolade <ad...@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 <pe...@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.edike@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 <di...@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.edike@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.edike@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*
>>
>>
>>
Re: Error Joining Two Tables In Apache Drill
Posted by Charles Givre <cg...@gmail.com>.
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 <pe...@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.edike@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 <di...@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.edike@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.checkAndThrowExcep
>> tion(Unknown
>> Source)
>>
>> at
>> com.mapr.drill.drill.dataengine.DRQryResultListener.getNextBatch(Unkno
>> wn
>> Source)
>>
>> at
>> com.mapr.drill.drill.dataengine.DRJDBCResultSet.doLoadRecordBatchData(
>> Unknown
>> Source)
>>
>> at
>> com.mapr.drill.drill.dataengine.DRJDBCResultSet.doMoveToNextRow(Unknow
>> 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(DelegatingStateme
>> nt.java:291)
>>
>> at
>> org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStateme
>> nt.java:291)
>>
>> at
>> org.apache.zeppelin.jdbc.JDBCInterpreter.executeSql(JDBCInterpreter.ja
>> va:581)
>>
>> at
>> org.apache.zeppelin.jdbc.JDBCInterpreter.interpret(JDBCInterpreter.jav
>> a:692)
>>
>> at
>> org.apache.zeppelin.interpreter.LazyOpenInterpreter.interpret(LazyOpen
>> Interpreter.java:97)
>>
>> at
>> org.apache.zeppelin.interpreter.remote.RemoteInterpreterServer$Interpr
>> etJob.jobRun(RemoteInterpreterServer.java:498)
>>
>> at org.apache.zeppelin.scheduler.Job.run(Job.java:175)
>>
>> at
>> org.apache.zeppelin.scheduler.ParallelScheduler$JobRunner.run(Parallel
>> Scheduler.java:162)
>>
>> at
>> java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511
>> )
>>
>> 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.edike@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*
>>
>>
>>
Re: Error Joining Two Tables In Apache Drill
Posted by Divya Gehlot <di...@gmail.com>.
you can use TRIM instead of LTRIM and the RTRIM
Thanks,
Divya
On 30 May 2018 at 22:57, Peter Edike <pe...@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.edike@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 <di...@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.edike@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.checkAndThrowExcep
> > tion(Unknown
> > Source)
> >
> > at
> > com.mapr.drill.drill.dataengine.DRQryResultListener.getNextBatch(Unkno
> > wn
> > Source)
> >
> > at
> > com.mapr.drill.drill.dataengine.DRJDBCResultSet.doLoadRecordBatchData(
> > Unknown
> > Source)
> >
> > at
> > com.mapr.drill.drill.dataengine.DRJDBCResultSet.doMoveToNextRow(Unknow
> > 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(DelegatingStateme
> > nt.java:291)
> >
> > at
> > org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStateme
> > nt.java:291)
> >
> > at
> > org.apache.zeppelin.jdbc.JDBCInterpreter.executeSql(JDBCInterpreter.ja
> > va:581)
> >
> > at
> > org.apache.zeppelin.jdbc.JDBCInterpreter.interpret(JDBCInterpreter.jav
> > a:692)
> >
> > at
> > org.apache.zeppelin.interpreter.LazyOpenInterpreter.interpret(LazyOpen
> > Interpreter.java:97)
> >
> > at
> > org.apache.zeppelin.interpreter.remote.RemoteInterpreterServer$Interpr
> > etJob.jobRun(RemoteInterpreterServer.java:498)
> >
> > at org.apache.zeppelin.scheduler.Job.run(Job.java:175)
> >
> > at
> > org.apache.zeppelin.scheduler.ParallelScheduler$JobRunner.run(Parallel
> > Scheduler.java:162)
> >
> > at
> > java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511
> > )
> >
> > 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.edike@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*
> >
> >
> >
>
RE: Error Joining Two Tables In Apache Drill
Posted by Peter Edike <pe...@interswitchgroup.com>.
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.edike@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 <di...@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.edike@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.checkAndThrowExcep
> tion(Unknown
> Source)
>
> at
> com.mapr.drill.drill.dataengine.DRQryResultListener.getNextBatch(Unkno
> wn
> Source)
>
> at
> com.mapr.drill.drill.dataengine.DRJDBCResultSet.doLoadRecordBatchData(
> Unknown
> Source)
>
> at
> com.mapr.drill.drill.dataengine.DRJDBCResultSet.doMoveToNextRow(Unknow
> 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(DelegatingStateme
> nt.java:291)
>
> at
> org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStateme
> nt.java:291)
>
> at
> org.apache.zeppelin.jdbc.JDBCInterpreter.executeSql(JDBCInterpreter.ja
> va:581)
>
> at
> org.apache.zeppelin.jdbc.JDBCInterpreter.interpret(JDBCInterpreter.jav
> a:692)
>
> at
> org.apache.zeppelin.interpreter.LazyOpenInterpreter.interpret(LazyOpen
> Interpreter.java:97)
>
> at
> org.apache.zeppelin.interpreter.remote.RemoteInterpreterServer$Interpr
> etJob.jobRun(RemoteInterpreterServer.java:498)
>
> at org.apache.zeppelin.scheduler.Job.run(Job.java:175)
>
> at
> org.apache.zeppelin.scheduler.ParallelScheduler$JobRunner.run(Parallel
> Scheduler.java:162)
>
> at
> java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511
> )
>
> 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.edike@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*
>
>
>
Re: Error Joining Two Tables In Apache Drill
Posted by Divya Gehlot <di...@gmail.com>.
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.edike@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.checkAndThrowException(Unknown
> Source)
>
> at
> com.mapr.drill.drill.dataengine.DRQryResultListener.getNextBatch(Unknown
> Source)
>
> at
> com.mapr.drill.drill.dataengine.DRJDBCResultSet.doLoadRecordBatchData(Unknown
> Source)
>
> at com.mapr.drill.drill.dataengine.DRJDBCResultSet.doMoveToNextRow(Unknown
> 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(DelegatingStatement.java:291)
>
> at
> org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:291)
>
> at
> org.apache.zeppelin.jdbc.JDBCInterpreter.executeSql(JDBCInterpreter.java:581)
>
> at
> org.apache.zeppelin.jdbc.JDBCInterpreter.interpret(JDBCInterpreter.java:692)
>
> at
> org.apache.zeppelin.interpreter.LazyOpenInterpreter.interpret(LazyOpenInterpreter.java:97)
>
> at
> org.apache.zeppelin.interpreter.remote.RemoteInterpreterServer$InterpretJob.jobRun(RemoteInterpreterServer.java:498)
>
> at org.apache.zeppelin.scheduler.Job.run(Job.java:175)
>
> at
> org.apache.zeppelin.scheduler.ParallelScheduler$JobRunner.run(ParallelScheduler.java:162)
>
> at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
>
> 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].
>
> ... 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.edike@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*
>
>
>
Re: Error Joining Two Tables In Apache Drill
Posted by Ted Dunning <te...@gmail.com>.
Also, do you have some data that looks like 'XXX'?
On Wed, May 30, 2018 at 1:18 PM Charles Givre <cg...@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 <pe...@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.edike@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 <cg...@gmail.com>
> > Sent: Monday, May 28, 2018 5:07 PM
> > To: user@drill.apache.org
> > Cc: Adedamola Kolade <ad...@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.edike@interswitchgroup.com> wrote:
> >>
> >> I GET VARCHAR
> >>
> >> Best regards,
> >> Peter Edike
> >>
> >> Senior Software Engineer
> >> Interswitch
> >>
> >> Tel. | Mobile. | IP Phone.
> >> Fax. | mailto:peter.edike@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 <cg...@gmail.com>
> >> Sent: Monday, May 28, 2018 4:59 PM
> >> To: user@drill.apache.org
> >> Cc: Adedamola Kolade <ad...@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.edike@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.edike@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 <cg...@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.edike@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.edike@interswitchgroup.com
> >>>> <ma...@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 <cgivre@gmail.com <ma...@gmail.com>>
> >>>> Sent: Monday, May 28, 2018 4:40 PM
> >>>> To: user@drill.apache.org <ma...@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.edike@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.edike@interswitchgroup.com
> >>>>> <ma...@interswitchgroup.com>
> >>>>> <mailto:peter.edike@interswitchgroup.com
> >>>>> <ma...@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
> >>>
> >>
> >
>
>
Re: Error Joining Two Tables In Apache Drill
Posted by Charles Givre <cg...@gmail.com>.
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 <pe...@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.edike@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 <cg...@gmail.com>
> Sent: Monday, May 28, 2018 5:07 PM
> To: user@drill.apache.org
> Cc: Adedamola Kolade <ad...@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 <pe...@interswitchgroup.com> wrote:
>>
>> I GET VARCHAR
>>
>> Best regards,
>> Peter Edike
>>
>> Senior Software Engineer
>> Interswitch
>>
>> Tel. | Mobile. | IP Phone.
>> Fax. | mailto:peter.edike@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 <cg...@gmail.com>
>> Sent: Monday, May 28, 2018 4:59 PM
>> To: user@drill.apache.org
>> Cc: Adedamola Kolade <ad...@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 <pe...@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.edike@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 <cg...@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 <pe...@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.edike@interswitchgroup.com
>>>> <ma...@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 <cgivre@gmail.com <ma...@gmail.com>>
>>>> Sent: Monday, May 28, 2018 4:40 PM
>>>> To: user@drill.apache.org <ma...@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 <pe...@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.edike@interswitchgroup.com
>>>>> <ma...@interswitchgroup.com>
>>>>> <mailto:peter.edike@interswitchgroup.com
>>>>> <ma...@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
>>>
>>
>
RE: Error Joining Two Tables In Apache Drill
Posted by Peter Edike <pe...@interswitchgroup.com>.
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.edike@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 <cg...@gmail.com>
Sent: Monday, May 28, 2018 5:07 PM
To: user@drill.apache.org
Cc: Adedamola Kolade <ad...@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 <pe...@interswitchgroup.com> wrote:
>
> I GET VARCHAR
>
> Best regards,
> Peter Edike
>
> Senior Software Engineer
> Interswitch
>
> Tel. | Mobile. | IP Phone.
> Fax. | mailto:peter.edike@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 <cg...@gmail.com>
> Sent: Monday, May 28, 2018 4:59 PM
> To: user@drill.apache.org
> Cc: Adedamola Kolade <ad...@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 <pe...@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.edike@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 <cg...@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 <pe...@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.edike@interswitchgroup.com
>>> <ma...@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 <cgivre@gmail.com <ma...@gmail.com>>
>>> Sent: Monday, May 28, 2018 4:40 PM
>>> To: user@drill.apache.org <ma...@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 <pe...@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.edike@interswitchgroup.com
>>>> <ma...@interswitchgroup.com>
>>>> <mailto:peter.edike@interswitchgroup.com
>>>> <ma...@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
>>
>
Re: Error Joining Two Tables In Apache Drill
Posted by Charles Givre <cg...@gmail.com>.
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 <pe...@interswitchgroup.com> wrote:
>
> I GET VARCHAR
>
> Best regards,
> Peter Edike
>
> Senior Software Engineer
> Interswitch
>
> Tel. | Mobile. | IP Phone.
> Fax. | mailto:peter.edike@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 <cg...@gmail.com>
> Sent: Monday, May 28, 2018 4:59 PM
> To: user@drill.apache.org
> Cc: Adedamola Kolade <ad...@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 <pe...@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.edike@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 <cg...@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 <pe...@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.edike@interswitchgroup.com
>>> <ma...@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 <cgivre@gmail.com <ma...@gmail.com>>
>>> Sent: Monday, May 28, 2018 4:40 PM
>>> To: user@drill.apache.org <ma...@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 <pe...@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.checkAndThrowExc
>>>> e
>>>> ption(Unknown Source) at
>>>> com.mapr.drill.drill.dataengine.DRQryResultListener.getNextBatch(Unk
>>>> n
>>>> own Source) at
>>>> com.mapr.drill.drill.dataengine.DRJDBCResultSet.doLoadRecordBatchDat
>>>> a
>>>> (Unknown Source) at
>>>> com.mapr.drill.drill.dataengine.DRJDBCResultSet.doMoveToNextRow(Unkn
>>>> 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(DelegatingState
>>>> m
>>>> ent.java:291) at
>>>> org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingState
>>>> 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(LazyOp
>>>> e
>>>> nInterpreter.java:97) at
>>>> org.apache.zeppelin.interpreter.remote.RemoteInterpreterServer$Inter
>>>> p
>>>> retJob.jobRun(RemoteInterpreterServer.java:498)
>>>> at org.apache.zeppelin.scheduler.Job.run(Job.java:175)
>>>> at
>>>> org.apache.zeppelin.scheduler.ParallelScheduler$JobRunner.run(Parall
>>>> 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(ThreadPoolExecuto
>>>> 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.edike@interswitchgroup.com
>>>> <ma...@interswitchgroup.com>
>>>> <mailto:peter.edike@interswitchgroup.com
>>>> <ma...@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
>>
>
RE: Error Joining Two Tables In Apache Drill
Posted by Peter Edike <pe...@interswitchgroup.com>.
I GET VARCHAR
Best regards,
Peter Edike
Senior Software Engineer
Interswitch
Tel. | Mobile. | IP Phone.
Fax. | mailto:peter.edike@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 <cg...@gmail.com>
Sent: Monday, May 28, 2018 4:59 PM
To: user@drill.apache.org
Cc: Adedamola Kolade <ad...@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 <pe...@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.edike@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 <cg...@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 <pe...@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.edike@interswitchgroup.com
>> <ma...@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 <cgivre@gmail.com <ma...@gmail.com>>
>> Sent: Monday, May 28, 2018 4:40 PM
>> To: user@drill.apache.org <ma...@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 <pe...@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.checkAndThrowExc
>>> e
>>> ption(Unknown Source) at
>>> com.mapr.drill.drill.dataengine.DRQryResultListener.getNextBatch(Unk
>>> n
>>> own Source) at
>>> com.mapr.drill.drill.dataengine.DRJDBCResultSet.doLoadRecordBatchDat
>>> a
>>> (Unknown Source) at
>>> com.mapr.drill.drill.dataengine.DRJDBCResultSet.doMoveToNextRow(Unkn
>>> 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(DelegatingState
>>> m
>>> ent.java:291) at
>>> org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingState
>>> 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(LazyOp
>>> e
>>> nInterpreter.java:97) at
>>> org.apache.zeppelin.interpreter.remote.RemoteInterpreterServer$Inter
>>> p
>>> retJob.jobRun(RemoteInterpreterServer.java:498)
>>> at org.apache.zeppelin.scheduler.Job.run(Job.java:175)
>>> at
>>> org.apache.zeppelin.scheduler.ParallelScheduler$JobRunner.run(Parall
>>> 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(ThreadPoolExecuto
>>> 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.edike@interswitchgroup.com
>>> <ma...@interswitchgroup.com>
>>> <mailto:peter.edike@interswitchgroup.com
>>> <ma...@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
>
Re: Error Joining Two Tables In Apache Drill
Posted by Charles Givre <cg...@gmail.com>.
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 <pe...@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.edike@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 <cg...@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 <pe...@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.edike@interswitchgroup.com
>> <ma...@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 <cgivre@gmail.com <ma...@gmail.com>>
>> Sent: Monday, May 28, 2018 4:40 PM
>> To: user@drill.apache.org <ma...@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 <pe...@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.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: peter.edike@interswitchgroup.com
>>> <ma...@interswitchgroup.com>
>>> <mailto:peter.edike@interswitchgroup.com
>>> <ma...@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
>
RE: Error Joining Two Tables In Apache Drill
Posted by Peter Edike <pe...@interswitchgroup.com>.
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.edike@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 <cg...@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 <pe...@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.edike@interswitchgroup.com
> <ma...@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 <cgivre@gmail.com <ma...@gmail.com>>
> Sent: Monday, May 28, 2018 4:40 PM
> To: user@drill.apache.org <ma...@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 <pe...@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.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: peter.edike@interswitchgroup.com
>> <ma...@interswitchgroup.com>
>> <mailto:peter.edike@interswitchgroup.com
>> <ma...@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
Re: Error Joining Two Tables In Apache Drill
Posted by Charles Givre <cg...@gmail.com>.
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 <pe...@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.edike@interswitchgroup.com <ma...@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 <cgivre@gmail.com <ma...@gmail.com>>
> Sent: Monday, May 28, 2018 4:40 PM
> To: user@drill.apache.org <ma...@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 <pe...@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.checkAndThrowException(Unknown Source)
>> at com.mapr.drill.drill.dataengine.DRQryResultListener.getNextBatch(Unknown Source)
>> at com.mapr.drill.drill.dataengine.DRJDBCResultSet.doLoadRecordBatchData(Unknown Source)
>> at com.mapr.drill.drill.dataengine.DRJDBCResultSet.doMoveToNextRow(Unknown 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(DelegatingStatement.java:291)
>> at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:291)
>> at org.apache.zeppelin.jdbc.JDBCInterpreter.executeSql(JDBCInterpreter.java:581)
>> at org.apache.zeppelin.jdbc.JDBCInterpreter.interpret(JDBCInterpreter.java:692)
>> at org.apache.zeppelin.interpreter.LazyOpenInterpreter.interpret(LazyOpenInterpreter.java:97)
>> at org.apache.zeppelin.interpreter.remote.RemoteInterpreterServer$InterpretJob.jobRun(RemoteInterpreterServer.java:498)
>> at org.apache.zeppelin.scheduler.Job.run(Job.java:175)
>> at org.apache.zeppelin.scheduler.ParallelScheduler$JobRunner.run(ParallelScheduler.java:162)
>> at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
>> 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: peter.edike@interswitchgroup.com <ma...@interswitchgroup.com> <mailto:peter.edike@interswitchgroup.com <ma...@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
RE: Error Joining Two Tables In Apache Drill
Posted by Peter Edike <pe...@interswitchgroup.com>.
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.edike@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:43:53 PM
-----Original Message-----
From: Charles Givre <cg...@gmail.com>
Sent: Monday, May 28, 2018 4:40 PM
To: 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 <pe...@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/>].
> at com.mapr.drill.drill.dataengine.DRQryResultListener.checkAndThrowException(Unknown Source)
> at com.mapr.drill.drill.dataengine.DRQryResultListener.getNextBatch(Unknown Source)
> at com.mapr.drill.drill.dataengine.DRJDBCResultSet.doLoadRecordBatchData(Unknown Source)
> at com.mapr.drill.drill.dataengine.DRJDBCResultSet.doMoveToNextRow(Unknown 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(DelegatingStatement.java:291)
> at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:291)
> at org.apache.zeppelin.jdbc.JDBCInterpreter.executeSql(JDBCInterpreter.java:581)
> at org.apache.zeppelin.jdbc.JDBCInterpreter.interpret(JDBCInterpreter.java:692)
> at org.apache.zeppelin.interpreter.LazyOpenInterpreter.interpret(LazyOpenInterpreter.java:97)
> at org.apache.zeppelin.interpreter.remote.RemoteInterpreterServer$InterpretJob.jobRun(RemoteInterpreterServer.java:498)
> at org.apache.zeppelin.scheduler.Job.run(Job.java:175)
> at org.apache.zeppelin.scheduler.ParallelScheduler$JobRunner.run(ParallelScheduler.java:162)
> at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
> 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/>].
> ... 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.edike@interswitchgroup.com <ma...@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
>
Re: Error Joining Two Tables In Apache Drill
Posted by Charles Givre <cg...@gmail.com>.
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 <pe...@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/>].
> at com.mapr.drill.drill.dataengine.DRQryResultListener.checkAndThrowException(Unknown Source)
> at com.mapr.drill.drill.dataengine.DRQryResultListener.getNextBatch(Unknown Source)
> at com.mapr.drill.drill.dataengine.DRJDBCResultSet.doLoadRecordBatchData(Unknown Source)
> at com.mapr.drill.drill.dataengine.DRJDBCResultSet.doMoveToNextRow(Unknown 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(DelegatingStatement.java:291)
> at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:291)
> at org.apache.zeppelin.jdbc.JDBCInterpreter.executeSql(JDBCInterpreter.java:581)
> at org.apache.zeppelin.jdbc.JDBCInterpreter.interpret(JDBCInterpreter.java:692)
> at org.apache.zeppelin.interpreter.LazyOpenInterpreter.interpret(LazyOpenInterpreter.java:97)
> at org.apache.zeppelin.interpreter.remote.RemoteInterpreterServer$InterpretJob.jobRun(RemoteInterpreterServer.java:498)
> at org.apache.zeppelin.scheduler.Job.run(Job.java:175)
> at org.apache.zeppelin.scheduler.ParallelScheduler$JobRunner.run(ParallelScheduler.java:162)
> at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
> 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/>].
> ... 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.edike@interswitchgroup.com <ma...@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
>