# failing query **URL:** \ **Category:** Toad Data Point & Toad Intelligence Central\ **Created:** [May 23, 2017, 7:03am UTC](https://forums.toadworld.com/t/failing-query/39502 "2017-05-23T07:03:56Z")\ **Posts on this page:** 17\ **Page:** 1
**Author:** ![mf1375](https://avatars.discourse-cdn.com/v4/letter/m/9de053/32.png) [@mf1375](https://forums.toadworld.com/u/mf1375)\ **Post date:** [May 23, 2017, 7:03am UTC](https://forums.toadworld.com/t/failing-query/39502/1 "2017-05-23T07:03:56Z")
I will need to be switching from Toad for Oracle to Toad Datapoint because of the new database is changing. I am trying to use an existing query fro Toad for Oracle and changing it with the new view owner and the table names - luckily the column names have stayed the same. The query needs to pull data from 6 different tables. there are only 2 fields/column name that all 6 tables have in common that needs to get correlated/joined together. I have tried to use the query builder but it works upto 5 tables. When I just paste the new sql into sql editor, it says a failure at a certain line. how do I find that line? it would be nice if it would go to that point where the failure is. I don’t profess to be an expert on sql, but I am fairly good at taking a good work sql and changing it to my needs. ---
**Author:** ![emsaxton](https://avatars.discourse-cdn.com/v4/letter/e/b5a626/32.png) [@emsaxton](https://forums.toadworld.com/u/emsaxton)\ **Post date:** [May 23, 2017, 7:18am UTC](https://forums.toadworld.com/t/failing-query/39502/2 "2017-05-23T07:18:32Z")
Usually if you double click on the error it will take you to the line. It also will put a big red circle with a white x in it where there’s an issue. **From:** mf1375 [[mailto:bounce-mf1375@toadworld.com](mailto:bounce-mf1375@toadworld.com)] **Sent:** Tuesday, May 23, 2017 9:04 AM **To:** [toaddatapoint@toadworld.com](mailto:toaddatapoint@toadworld.com) **Subject:** [Toad Data Point - Discussion Forum] failing query **[failing query](http://www.toadworld.com/products/toad-data-point/f/36/t/32611)** Thread created by mf1375 I will need to be switching from Toad for Oracle to Toad Datapoint because of the new database is changing. I am trying to use an existing query fro Toad for Oracle and changing it with the new view owner and the table names - luckily the column names have stayed the same. The query needs to pull data from 6 different tables. there are only 2 fields/column name that all 6 tables have in common that needs to get correlated/joined together. I have tried to use the query builder but it works upto 5 tables. When I just paste the new sql into sql editor, it says a failure at a certain line. how do I find that line? it would be nice if it would go to that point where the failure is. I don’t profess to be an expert on sql, but I am fairly good at taking a good work sql and changing it to my needs. To reply, please reply-all to this email. [Stop receiving emails](http://www.toadworld.com/products/toad-data-point/f/36/t/32611?subscribe=false) on this subject. Or [ Unsubscribe from Toad Data Point Forum](http://www.toadworld.com/products/toad-data-point/f/36?subscribe=false) notifications altogether. Toad Data Point - Discussion Forum [Flag](http://www.toadworld.com/products/toad-data-point/f/36/t/32611?AbuseContentId=ff781bc5-c97c-4ab2-858b-21d1648e15eb&AbuseContentTypeId=46448885-d0e6-4133-bbfb-f0cd7b0fd6f7&AbuseFlag=true) this post as spam/abuse. ---
**Author:** ![AlekseyBazhenov](https://avatars.discourse-cdn.com/v4/letter/a/71e660/32.png) [@AlekseyBazhenov](https://forums.toadworld.com/u/AlekseyBazhenov)\ **Post date:** [May 23, 2017, 10:04am UTC](https://forums.toadworld.com/t/failing-query/39502/3 "2017-05-23T10:04:18Z")
Could you provide more information? Could you post the original query, the changed one and screenshot with the error? There is no limit on number of tables in Query Builder. What happened when you added the 6-th table to Query builder? Could you post the screenshot with Query builder and all added tables? Regards Aleksey ---
**Author:** ![mf1375](https://avatars.discourse-cdn.com/v4/letter/m/9de053/32.png) [@mf1375](https://forums.toadworld.com/u/mf1375)\ **Post date:** [May 23, 2017, 10:17am UTC](https://forums.toadworld.com/t/failing-query/39502/4 "2017-05-23T10:17:19Z")
This has been running for 90 minutes. My sql in Toad for Oracle has probably double the amount of columns and completes in 8 minutes SELECT MME\_MAF.datetime , MME\_MAF.mme ``` ,sum(MME_MAF.vs_attpaging_firstattempt) ,sum(MME_MAF.vs_nbrpagingto_firstattempt) ,sum(MME_MAF.vs_attpaging_secondattempt) ,sum(MME_MAF.vs_nbrpagingto_secondattempt) ,sum(MME_MAF.vs_nbrsuccessattachrequests) ,sum(MME_MAF.vs_attattachrequests) ,sum(MME_MAF_1.VS_NBRFAILATACHREQ_ILLEGALME) ,sum(MME_MAF_1.VS_NBRFLATCHREQS_EPSSRVCNTALW) ,sum(MME_MAF_1.VS_NBRFLATHRQ_EPSNDNONEPSNTALW) ,sum(MME_MAF_1.VS_NBRFAILATCHREQS_PLMNNTALOW) ,sum(MME_MAF_1.VS_NBRFAILATCHREQS_TANTALOW) ,sum(MME_MAF_1.VS_NBRFLATCHRQ_ROAMNGNTALWEDTA) ,sum(MME_MAF_1.VS_NBRFLATCHRQ_EPSSRVNTALWPLMN) ,sum(MME_MAF_2_1.VS_ATCESMFL_MISINGORUNKNWAPN) ,sum(MME_MAF_2_1.VS_ATCESMFL_UNKNOWNPDNTYPE) ,sum(MME_MAF_2_1.VS_ATCESMFL_SVCOPTNTSUPORTED) ,sum(MME_MAF_2_1.VS_ATCESMFL_SVCOPTNTSUBSCRBD) ,sum(MME_MAF1.VS_RAUINTRASGWATTS3) ,sum(MME_MAF1.VS_RAUINTRASGWSUCCS3) ,sum(MME_MAF1.VS_RAUINTERSGWATTS3) ,sum(MME_MAF1.VS_RAUINTERSGWSUCCS3) ,sum(MME_MAF2.VS_TAUATTS3) ,sum(MME_MAF2.VS_TAUSUCCS3) ,sum(MME_MAF_1_1.VS_NBRFAILEDATTACHREQUESTS_SUM) ,sum(MME_MAF_1_1.VS_ATTNONEPSATTACH) ,sum(MME_MAF_1_1.VS_NBRFAILNONEPSATCH_ILLEGALUE) ``` FROM ``` ((((PDW_ALUMME_VIEWS.MME_MAF MME_MAF INNER ``` JOIN ``` PDW_ALUMME_VIEWS.MME_MAF2 MME_MAF2 ON (MME_MAF.datetime = MME_MAF2.DATETIME) AND (MME_MAF.mme = MME_MAF2.MME)) INNER ``` JOIN ``` PDW_ALUMME_VIEWS.MME_MAF_1 MME_MAF_1 ON (MME_MAF.datetime = MME_MAF_1.DATETIME) AND (MME_MAF.mme = MME_MAF_1.MME)) INNER ``` JOIN ``` PDW_ALUMME_VIEWS.MME_MAF_2_1 MME_MAF_2_1 ON (MME_MAF.datetime = MME_MAF_2_1.DATETIME) AND (MME_MAF.mme = MME_MAF_2_1.MME)) INNER ``` JOIN ``` PDW_ALUMME_VIEWS.MME_MAF1 MME_MAF1 ON (MME_MAF.datetime = MME_MAF1.DATETIME) AND (MME_MAF.mme = MME_MAF1.MME)) INNER ``` JOIN ``` PDW_ALUMME_VIEWS.MME_MAF_1_1 MME_MAF_1_1 ON (MME_MAF.datetime = MME_MAF_1_1.DATETIME) AND (MME_MAF.mme = MME_MAF_1_1.MME) ``` where MME\_MAF.datetime \>= trunc(sysdate- **6** ) group by MME\_MAF.datetime,MME\_MAF.mme _Mark Fringer_ _AT&T Network Operations_ _Operational Certification and Advanced Technical Support (OCATS)_ _Nokia (fALU) Core_ _708-240-7598 - SKYPE Number_ _815-715-8048 - Cell Number_ - - - - This email and any files transmitted with it are AT&T property, are confidential, and are intended solely for the use of the individual or entity to whom this email is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender and delete this message immediately from your computer. Any other use, retention, dissemination, forwarding, printing or copying of this email is strictly prohibited. **From:** Aleksey Bazhenov [[mailto:bounce-AlekseyBazhenov@toadworld.com](mailto:bounce-AlekseyBazhenov@toadworld.com)] **Sent:** Tuesday, May 23, 2017 12:05 PM **To:** [toaddatapoint@toadworld.com](mailto:toaddatapoint@toadworld.com) **Subject:** RE: [Toad Data Point - Discussion Forum] failing query **[RE: failing query](https://urldefense.proofpoint.com/v2/url?u=http-3A__www.toadworld.com_products_toad-2Ddata-2Dpoint_f_36_p_32611_65993-2365993&d=DwMFaQ&c=LFYZ-o9_HUMeMTSQicvjIg&r=spEHEtGQcezYM0XDc_aOZg&m=fNZk8JHdR_2CTwX_kgDzircUX-nw3kTKT7W1ICI0eJk&s=lg9E2w9Lha2xaiUQw3z7PsdjzM75B_-g5b7KND39_EI&e=)** Reply by Aleksey Bazhenov Could you provide more information? Could you post the original query, the changed one and screenshot with the error? There is no limit on number of tables in Query Builder. What happened when you added the 6-th table to Query builder? Could you post the screenshot with Query builder and all added tables? Regards Aleksey To reply, please reply-all to this email. [Stop receiving emails](https://urldefense.proofpoint.com/v2/url?u=http-3A__www.toadworld.com_products_toad-2Ddata-2Dpoint_f_36_t_32611-3Fsubscribe-3Dfalse&d=DwMFaQ&c=LFYZ-o9_HUMeMTSQicvjIg&r=spEHEtGQcezYM0XDc_aOZg&m=fNZk8JHdR_2CTwX_kgDzircUX-nw3kTKT7W1ICI0eJk&s=cEwPLDHiJ4IvcFqJxrO-mg8C1zXy8wZr6tB6KvhBIAI&e=) on this subject. Or [ Unsubscribe from Toad Data Point Forum](https://urldefense.proofpoint.com/v2/url?u=http-3A__www.toadworld.com_products_toad-2Ddata-2Dpoint_f_36-3Fsubscribe-3Dfalse&d=DwMFaQ&c=LFYZ-o9_HUMeMTSQicvjIg&r=spEHEtGQcezYM0XDc_aOZg&m=fNZk8JHdR_2CTwX_kgDzircUX-nw3kTKT7W1ICI0eJk&s=foQrR0DMm0ChSMXdNu92-J3NkuLzHF89YrIZXU1_TXA&e=) notifications altogether. Toad Data Point - Discussion Forum [Flag](https://urldefense.proofpoint.com/v2/url?u=http-3A__www.toadworld.com_products_toad-2Ddata-2Dpoint_f_36_p_32611_65993-3FAbuseContentId-3Dcfabae7e-2D7917-2D41b7-2Daed2-2Dee68981b407e-26AbuseContentTypeId-3Df586769b-2D0822-2D468a-2Db7f3-2Da94d480ed9b0-26AbuseFlag-3Dtrue&d=DwMFaQ&c=LFYZ-o9_HUMeMTSQicvjIg&r=spEHEtGQcezYM0XDc_aOZg&m=fNZk8JHdR_2CTwX_kgDzircUX-nw3kTKT7W1ICI0eJk&s=7SJSppspbiTis4dzU6zIYRrun-4J1vhdzwrucLIOgR8&e=) this post as spam/abuse. ---
**Author:** ![AlekseyBazhenov](https://avatars.discourse-cdn.com/v4/letter/a/71e660/32.png) [@AlekseyBazhenov](https://forums.toadworld.com/u/AlekseyBazhenov)\ **Post date:** [May 23, 2017, 11:35am UTC](https://forums.toadworld.com/t/failing-query/39502/5 "2017-05-23T11:35:33Z")
Could you compare the execution plans of this query and the original one? Queries are executed on Oracle servers and they do not depend much on client applications. I’d suggest checking out if foreign key columns have indexes. Regards Aleksey ---
**Author:** ![Mark\_Kurtz](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/mark_kurtz/32/10_2.png) [@Mark\_Kurtz](https://forums.toadworld.com/u/Mark_Kurtz)\ **Post date:** [May 23, 2017, 11:40am UTC](https://forums.toadworld.com/t/failing-query/39502/6 "2017-05-23T11:40:58Z")
you mention that “the new database is changing”. Does that mean you are moving away from Oracle to something else? That could have an effect on the query, meaning is the new database platform optimized and tuned for these types of queries. -Mark ---
**Author:** ![mf1375](https://avatars.discourse-cdn.com/v4/letter/m/9de053/32.png) [@mf1375](https://forums.toadworld.com/u/mf1375)\ **Post date:** [May 23, 2017, 11:46am UTC](https://forums.toadworld.com/t/failing-query/39502/7 "2017-05-23T11:46:38Z")
From Oracle to Vertica _Mark Fringer_ _AT&T Network Operations_ _Operational Certification and Advanced Technical Support (OCATS)_ _Nokia (fALU) Core_ _708-240-7598 - SKYPE Number_ _815-715-8048 - Cell Number_ - - - - This email and any files transmitted with it are AT&T property, are confidential, and are intended solely for the use of the individual or entity to whom this email is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender and delete this message immediately from your computer. Any other use, retention, dissemination, forwarding, printing or copying of this email is strictly prohibited. **From:** Mark Kurtz [[mailto:bounce-Mark\_Kurtz@toadworld.com](mailto:bounce-Mark_Kurtz@toadworld.com)] **Sent:** Tuesday, May 23, 2017 1:44 PM **To:** [toaddatapoint@toadworld.com](mailto:toaddatapoint@toadworld.com) **Subject:** RE: [Toad Data Point - Discussion Forum] failing query **[RE: failing query](https://urldefense.proofpoint.com/v2/url?u=http-3A__www.toadworld.com_products_toad-2Ddata-2Dpoint_f_36_p_32611_65997-2365997&d=DwMFaQ&c=LFYZ-o9_HUMeMTSQicvjIg&r=spEHEtGQcezYM0XDc_aOZg&m=GLmVahwi49U9Es7GUjUkq-0UhbukDF2xmuA5XbTBnFM&s=EVZHfMlUx5BalNoXsJiV84FgIIudctfFOnMPmOV06EY&e=)** Reply by Mark Kurtz you mention that “the new database is changing”. Does that mean you are moving away from Oracle to something else? That could have an effect on the query, meaning is the new database platform optimized and tuned for these types of queries. -Mark To reply, please reply-all to this email. [Stop receiving emails](https://urldefense.proofpoint.com/v2/url?u=http-3A__www.toadworld.com_products_toad-2Ddata-2Dpoint_f_36_t_32611-3Fsubscribe-3Dfalse&d=DwMFaQ&c=LFYZ-o9_HUMeMTSQicvjIg&r=spEHEtGQcezYM0XDc_aOZg&m=GLmVahwi49U9Es7GUjUkq-0UhbukDF2xmuA5XbTBnFM&s=Vq2nhIboCW6XxAlauKQXrq5S1v8t5DxMMSFOQwIlak4&e=) on this subject. Or [ Unsubscribe from Toad Data Point Forum](https://urldefense.proofpoint.com/v2/url?u=http-3A__www.toadworld.com_products_toad-2Ddata-2Dpoint_f_36-3Fsubscribe-3Dfalse&d=DwMFaQ&c=LFYZ-o9_HUMeMTSQicvjIg&r=spEHEtGQcezYM0XDc_aOZg&m=GLmVahwi49U9Es7GUjUkq-0UhbukDF2xmuA5XbTBnFM&s=nFIASjoQ7cF0xL5zBP1ZI9ARftN3ylXN4Wq9RVW5eIQ&e=) notifications altogether. Toad Data Point - Discussion Forum [Flag](https://urldefense.proofpoint.com/v2/url?u=http-3A__www.toadworld.com_products_toad-2Ddata-2Dpoint_f_36_p_32611_65997-3FAbuseContentId-3Daf5dfb42-2Deb6b-2D424f-2Da521-2D1c419c91efdf-26AbuseContentTypeId-3Df586769b-2D0822-2D468a-2Db7f3-2Da94d480ed9b0-26AbuseFlag-3Dtrue&d=DwMFaQ&c=LFYZ-o9_HUMeMTSQicvjIg&r=spEHEtGQcezYM0XDc_aOZg&m=GLmVahwi49U9Es7GUjUkq-0UhbukDF2xmuA5XbTBnFM&s=HspwoQngNjFndOhoRS-ktqeNnxvx0zplZh-8_5lC5Ro&e=) this post as spam/abuse. ---
**Author:** ![AlekseyBazhenov](https://avatars.discourse-cdn.com/v4/letter/a/71e660/32.png) [@AlekseyBazhenov](https://forums.toadworld.com/u/AlekseyBazhenov)\ **Post date:** [May 24, 2017, 11:33am UTC](https://forums.toadworld.com/t/failing-query/39502/8 "2017-05-24T11:33:50Z")
Hi Mark, I suggest to verify that the DATETIME and MME columns in all tables are indexed. Regards Aleksey ---
**Author:** ![mf1375](https://avatars.discourse-cdn.com/v4/letter/m/9de053/32.png) [@mf1375](https://forums.toadworld.com/u/mf1375)\ **Post date:** [May 24, 2017, 1:09pm UTC](https://forums.toadworld.com/t/failing-query/39502/9 "2017-05-24T13:09:15Z")
I’ve been playing with this to prove/disprove what is going on. There is 5 different INNER JOIN used for the 6 different tables. Each table has a variety number of columns that I pull. It all works fine in a somewhat reasonable time – within 10 minutes – when I use 4 INNER JOIN with 5 tables. It doesn’t matter which 5 tables. Once I add the 6th table it takes very long time – well over an hour. So I stop it. The 6th table only has 2 or 3 columns. _Mark Fringer_ _AT&T Network Operations_ _Operational Certification and Advanced Technical Support (OCATS)_ _Nokia (fALU) Core_ _708-240-7598 - SKYPE Number_ _815-715-8048 - Cell Number_ - - - - This email and any files transmitted with it are AT&T property, are confidential, and are intended solely for the use of the individual or entity to whom this email is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender and delete this message immediately from your computer. Any other use, retention, dissemination, forwarding, printing or copying of this email is strictly prohibited. **From:** Aleksey Bazhenov [[mailto:bounce-AlekseyBazhenov@toadworld.com](mailto:bounce-AlekseyBazhenov@toadworld.com)] **Sent:** Wednesday, May 24, 2017 1:35 PM **To:** [toaddatapoint@toadworld.com](mailto:toaddatapoint@toadworld.com) **Subject:** RE: [Toad Data Point - Discussion Forum] failing query **[RE: failing query](https://urldefense.proofpoint.com/v2/url?u=http-3A__www.toadworld.com_products_toad-2Ddata-2Dpoint_f_36_p_32611_66017-2366017&d=DwMFaQ&c=LFYZ-o9_HUMeMTSQicvjIg&r=spEHEtGQcezYM0XDc_aOZg&m=Anf-8wM2eEnCPda2Eu4HGFZW20-f3tsKSC_2qXqCFf4&s=AYLqmLQnPtfholYPOhtr_IAmkAMfq_gPDwJ3yLYjvZA&e=)** Reply by Aleksey Bazhenov Hi Mark, I suggest to verify that the DATETIME and MME columns in all tables are indexed. Regards Aleksey To reply, please reply-all to this email. [Stop receiving emails](https://urldefense.proofpoint.com/v2/url?u=http-3A__www.toadworld.com_products_toad-2Ddata-2Dpoint_f_36_t_32611-3Fsubscribe-3Dfalse&d=DwMFaQ&c=LFYZ-o9_HUMeMTSQicvjIg&r=spEHEtGQcezYM0XDc_aOZg&m=Anf-8wM2eEnCPda2Eu4HGFZW20-f3tsKSC_2qXqCFf4&s=VsRIstTYIYTS8sz7-Atzm-T_pdAB_-3K96OWNbsvNLk&e=) on this subject. Or [ Unsubscribe from Toad Data Point Forum](https://urldefense.proofpoint.com/v2/url?u=http-3A__www.toadworld.com_products_toad-2Ddata-2Dpoint_f_36-3Fsubscribe-3Dfalse&d=DwMFaQ&c=LFYZ-o9_HUMeMTSQicvjIg&r=spEHEtGQcezYM0XDc_aOZg&m=Anf-8wM2eEnCPda2Eu4HGFZW20-f3tsKSC_2qXqCFf4&s=_YB580Pdcnfktc2QCecq7Ubb_cHgoCxWRv6dxbUhLak&e=) notifications altogether. Toad Data Point - Discussion Forum [Flag](https://urldefense.proofpoint.com/v2/url?u=http-3A __www.toadworld.com_products_toad-2Ddata-2Dpoint_f_36_p_32611_66017-3FAbuseContentId-3Dd852e394-2Db4b4-2D4b5e-2D9eb4-2Da2fa9b88af33-26AbuseContentTypeId-3Df586769b-2D0822-2D468a-2Db7f3-2Da94d480ed9b0-26AbuseFlag-3Dtrue&d=DwMFaQ&c=LFYZ-o9_HUMeMTSQicvjIg&r=spEHEtGQcezYM0XDc_aOZg&m=Anf-8wM2eEnCPda2Eu4HGFZW20-f3tsKSC_2qXqCFf4&s=58eiO-ZALOwbQQi7ilUQjkgojzlK3__ 9FHc7Bfqgfso&e=) this post as spam/abuse. ---
**Author:** ![armand\_charest\_31540](https://avatars.discourse-cdn.com/v4/letter/a/e0b2c6/32.png) [@armand\_charest\_31540](https://forums.toadworld.com/u/armand_charest_31540)\ **Post date:** [May 24, 2017, 1:30pm UTC](https://forums.toadworld.com/t/failing-query/39502/10 "2017-05-24T13:30:35Z")
MME\_MAF.datetime \>= trunc(sysdate- **6** ) In this portion of your code is seems to be using a date and time field. example: 05/24/2017 10:52:21 then you are truncating today’s date which would result in 05/24/2017 12:00:00 AM. The fields do not hold the same date and time data. You would have to truncate the MME\_MAF.datetime field to match. I am also wondering about the number of columns that are being brought in. An inner join on a table like you are doing will make all fields available to the query. What would happen if you constructed your query so only the fields you use are actually used in the query results and for linking to maybe other tables? Just some things to think about. ---
**Author:** ![AlekseyBazhenov](https://avatars.discourse-cdn.com/v4/letter/a/71e660/32.png) [@AlekseyBazhenov](https://forums.toadworld.com/u/AlekseyBazhenov)\ **Post date:** [May 24, 2017, 1:32pm UTC](https://forums.toadworld.com/t/failing-query/39502/11 "2017-05-24T13:32:50Z")
Number of columns almost does not affect execution time but the number of rows that meets the filter does. It looks like Vertica query optimizer does not like more than 4 joins (5 tables) . I would suggest to change order of joins (you can use brackets to change the order) - join 3 table , join second 3 tables and now join these two joins. Or you can use subgueries like SELECT …. FROM subquery\_1 inner join subquery\_2 on … Subquery\_1 is join of the first 3 tables and subquery\_2 is the join of the rest. Aleksey ---
**Author:** ![mf1375](https://avatars.discourse-cdn.com/v4/letter/m/9de053/32.png) [@mf1375](https://forums.toadworld.com/u/mf1375)\ **Post date:** [May 25, 2017, 8:41am UTC](https://forums.toadworld.com/t/failing-query/39502/12 "2017-05-25T08:41:41Z")
Would something other than an “inner join” be better to use? In my test I only am using about 1/3 of the columns that I need for the real thing _Mark Fringer_ _AT&T Network Operations_ _Operational Certification and Advanced Technical Support (OCATS)_ _Nokia (fALU) Core_ _708-240-7598 - SKYPE Number_ _815-715-8048 - Cell Number_ - - - - This email and any files transmitted with it are AT&T property, are confidential, and are intended solely for the use of the individual or entity to whom this email is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender and delete this message immediately from your computer. Any other use, retention, dissemination, forwarding, printing or copying of this email is strictly prohibited. **From:** armand\_charest\_31540 [[mailto:bounce-armand\_charest\_31540@toadworld.com](mailto:bounce-armand_charest_31540@toadworld.com)] **Sent:** Wednesday, May 24, 2017 3:56 PM **To:** [toaddatapoint@toadworld.com](mailto:toaddatapoint@toadworld.com) **Subject:** RE: [Toad Data Point - Discussion Forum] failing query **[RE: failing query](https://urldefense.proofpoint.com/v2/url?u=http-3A__www.toadworld.com_products_toad-2Ddata-2Dpoint_f_36_p_32611_66020-2366020&d=DwMFaQ&c=LFYZ-o9_HUMeMTSQicvjIg&r=spEHEtGQcezYM0XDc_aOZg&m=CakGpQWsELpIDezQRdCQ3E_Rjt8SNZrF-OyWAzlg5oo&s=97GgB8XkMzUzYrFc3rP64377BmGG7SRab7jsaEbGcU4&e=)** Reply by armand\_charest\_31540 MME\_MAF.datetime \>= trunc(sysdate- **6** ) In this portion of your code is seems to be using a date and time field. example: 05/24/2017 10:52:21 then you are truncating today’s date which would result in 05/24/2017 12:00:00 AM. The fields do not hold the same date and time data. You would have to truncate the MME\_MAF.datetime field to match. I am also wondering about the number of columns that are being brought in. An inner join on a table like you are doing will make all fields available to the query. What would happen if you constructed your query so only the fields you use are actually used in the query results and for linking to maybe other tables? Just some things to think about. To reply, please reply-all to this email. [Stop receiving emails](https://urldefense.proofpoint.com/v2/url?u=http-3A__www.toadworld.com_products_toad-2Ddata-2Dpoint_f_36_t_32611-3Fsubscribe-3Dfalse&d=DwMFaQ&c=LFYZ-o9_HUMeMTSQicvjIg&r=spEHEtGQcezYM0XDc_aOZg&m=CakGpQWsELpIDezQRdCQ3E_Rjt8SNZrF-OyWAzlg5oo&s=rWYvC28fpSFivxb3qYfVrY040bcpNndy7z_kPFogKUw&e=) on this subject. Or [ Unsubscribe from Toad Data Point Forum](https://urldefense.proofpoint.com/v2/url?u=http-3A__www.toadworld.com_products_toad-2Ddata-2Dpoint_f_36-3Fsubscribe-3Dfalse&d=DwMFaQ&c=LFYZ-o9_HUMeMTSQicvjIg&r=spEHEtGQcezYM0XDc_aOZg&m=CakGpQWsELpIDezQRdCQ3E_Rjt8SNZrF-OyWAzlg5oo&s=PO3Ymn0NhgY7c1GAybARi3pDL_FDU38zAjilhojTbS4&e=) notifications altogether. Toad Data Point - Discussion Forum [Flag](https://urldefense.proofpoint.com/v2/url?u=http-3A__www.toadworld.com_products_toad-2Ddata-2Dpoint_f_36_p_32611_66020-3FAbuseContentId-3D1f8966be-2D5f26-2D459b-2D8cd2-2D4fa43aebda8a-26AbuseContentTypeId-3Df586769b-2D0822-2D468a-2Db7f3-2Da94d480ed9b0-26AbuseFlag-3Dtrue&d=DwMFaQ&c=LFYZ-o9_HUMeMTSQicvjIg&r=spEHEtGQcezYM0XDc_aOZg&m=CakGpQWsELpIDezQRdCQ3E_Rjt8SNZrF-OyWAzlg5oo&s=VyBHfKbIrrU1pFO5M7XrWn6PXe6s5HXRsj8yuFO7Vsc&e=) this post as spam/abuse. ---
**Author:** ![mf1375](https://avatars.discourse-cdn.com/v4/letter/m/9de053/32.png) [@mf1375](https://forums.toadworld.com/u/mf1375)\ **Post date:** [May 25, 2017, 8:43am UTC](https://forums.toadworld.com/t/failing-query/39502/13 "2017-05-25T08:43:45Z")
I am not that good at writing code like that……can you show me an example with this code? _Mark Fringer_ _AT&T Network Operations_ _Operational Certification and Advanced Technical Support (OCATS)_ _Nokia (fALU) Core_ _708-240-7598 - SKYPE Number_ _815-715-8048 - Cell Number_ - - - - This email and any files transmitted with it are AT&T property, are confidential, and are intended solely for the use of the individual or entity to whom this email is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender and delete this message immediately from your computer. Any other use, retention, dissemination, forwarding, printing or copying of this email is strictly prohibited. **From:** Aleksey Bazhenov [[mailto:bounce-AlekseyBazhenov@toadworld.com](mailto:bounce-AlekseyBazhenov@toadworld.com)] **Sent:** Wednesday, May 24, 2017 3:34 PM **To:** [toaddatapoint@toadworld.com](mailto:toaddatapoint@toadworld.com) **Subject:** RE: [Toad Data Point - Discussion Forum] failing query **[RE: failing query](https://urldefense.proofpoint.com/v2/url?u=http-3A__www.toadworld.com_products_toad-2Ddata-2Dpoint_f_36_p_32611_66021-2366021&d=DwMFaQ&c=LFYZ-o9_HUMeMTSQicvjIg&r=spEHEtGQcezYM0XDc_aOZg&m=qmLPBPRWhLtibKreZZsq0ZXrY4-Yut_UlUI2nSvNCxo&s=fJMs0-e1IzWUKuBy6VaKcjEkyLmUsjaoJrCKf9nBVD4&e=)** Reply by Aleksey Bazhenov Number of columns almost does not affect execution time but the number of rows that meets the filter does. It looks like Vertica query optimizer does not like more than 4 joins (5 tables) . I would suggest to change order of joins (you can use brackets to change the order) - join 3 table , join second 3 tables and now join these two joins. Or you can use subgueries like SELECT …. FROM subquery\_1 inner join subquery\_2 on … Subquery\_1 is join of the first 3 tables and subquery\_2 is the join of the rest. Aleksey To reply, please reply-all to this email. [Stop receiving emails](https://urldefense.proofpoint.com/v2/url?u=http-3A__www.toadworld.com_products_toad-2Ddata-2Dpoint_f_36_t_32611-3Fsubscribe-3Dfalse&d=DwMFaQ&c=LFYZ-o9_HUMeMTSQicvjIg&r=spEHEtGQcezYM0XDc_aOZg&m=qmLPBPRWhLtibKreZZsq0ZXrY4-Yut_UlUI2nSvNCxo&s=c2Bm9KMD6KhDmzx47F5sbch24ipQDHCeWXi6PBOrBg8&e=) on this subject. Or [ Unsubscribe from Toad Data Point Forum](https://urldefense.proofpoint.com/v2/url?u=http-3A__www.toadworld.com_products_toad-2Ddata-2Dpoint_f_36-3Fsubscribe-3Dfalse&d=DwMFaQ&c=LFYZ-o9_HUMeMTSQicvjIg&r=spEHEtGQcezYM0XDc_aOZg&m=qmLPBPRWhLtibKreZZsq0ZXrY4-Yut_UlUI2nSvNCxo&s=QhFH3NVZxMRv7nyUl6TzgVbV13zhRFYo_3oZ7yjsq_g&e=) notifications altogether. Toad Data Point - Discussion Forum [Flag](https://urldefense.proofpoint.com/v2/url?u=http-3A__www.toadworld.com_products_toad-2Ddata-2Dpoint_f_36_p_32611_66021-3FAbuseContentId-3Dae9ac493-2Df702-2D43d5-2D9087-2D795538ec2e25-26AbuseContentTypeId-3Df586769b-2D0822-2D468a-2Db7f3-2Da94d480ed9b0-26AbuseFlag-3Dtrue&d=DwMFaQ&c=LFYZ-o9_HUMeMTSQicvjIg&r=spEHEtGQcezYM0XDc_aOZg&m=qmLPBPRWhLtibKreZZsq0ZXrY4-Yut_UlUI2nSvNCxo&s=QfAp_Gfc30SDQYtzqUdZUpIQf6mM7kHBMGRx9ZOTLjE&e=) this post as spam/abuse. ---
**Author:** ![AlekseyBazhenov](https://avatars.discourse-cdn.com/v4/letter/a/71e660/32.png) [@AlekseyBazhenov](https://forums.toadworld.com/u/AlekseyBazhenov)\ **Post date:** [May 25, 2017, 10:14am UTC](https://forums.toadworld.com/t/failing-query/39502/14 "2017-05-25T10:14:09Z")
It should be something likes the following. Of course, you have to put columns you need and make sure that the subquery\_1 and subquery\_2 can be executed. SELECT all\_columns\_you\_need FROM ( SELECT MME\_MAF.datetime as datetime, MME\_MAF.MME as MME, _other_ columns\_you\_need\_1 FROM ((PDW\_ALUMME\_VIEWS.MME\_MAF MME\_MAF INNER JOIN PDW\_ALUMME\_VIEWS.MME\_MAF2 MME\_MAF2 ON (MME\_MAF.datetime = MME\_MAF2.DATETIME) AND (MME\_MAF.mme = MME\_MAF2.MME)) INNER JOIN PDW\_ALUMME\_VIEWS.MME\_MAF\_1 MME\_MAF\_1 ON (MME\_MAF.datetime = MME\_MAF\_1.DATETIME) AND (MME\_MAF.mme = MME\_MAF\_1.MME)) ) subquery\_1 INNER JOIN ( SELECT MME\_MAF\_2\_1.datetime as datetime, MME\_MAF\_2\_1.MME as MME, _other_ columns\_you\_need\_2 FROM PDW\_ALUMME\_VIEWS.MME\_MAF\_2\_1 MME\_MAF\_2\_1 INNER JOIN PDW\_ALUMME\_VIEWS.MME\_MAF1 MME\_MAF1 ON (MME\_MAF\_2\_1.datetime = MME\_MAF1.DATETIME) AND (MME\_MAF\_2\_1.MME = MME\_MAF1.MME)) INNER JOIN PDW\_ALUMME\_VIEWS.MME\_MAF\_1\_1 MME\_MAF\_1\_1 ON (MME\_MAF\_2\_1.datetime = MME\_MAF\_1\_1.DATETIME) AND (MME\_MAF\_2\_1.mme = MME\_MAF\_1\_1.MME) ) subquery\_2 ON ON (subquery\_1.datetime = subquery\_2.DATETIME) AND (subquery\_1.mme = subquery\_2.MME)) ---
**Author:** ![GregDavis11009](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/gregdavis11009/32/122_2.png) [@GregDavis11009](https://forums.toadworld.com/u/GregDavis11009)\ **Post date:** [May 25, 2017, 10:55am UTC](https://forums.toadworld.com/t/failing-query/39502/15 "2017-05-25T10:55:19Z")
How did Aleksey’s suggestion work? Putting parantheses around your joins forces the optimizer to perform the inner joins first and then the outer joins. It can substanially improve performance in some situations. ---
**Author:** ![mf1375](https://avatars.discourse-cdn.com/v4/letter/m/9de053/32.png) [@mf1375](https://forums.toadworld.com/u/mf1375)\ **Post date:** [May 26, 2017, 9:29am UTC](https://forums.toadworld.com/t/failing-query/39502/16 "2017-05-26T09:29:40Z")
THANKS for the help. Having 2 subqueries appears to be the solution. That means ALL the formatting has to be correct _Mark Fringer_ _AT&T Network Operations_ _Operational Certification and Advanced Technical Support (OCATS)_ _Nokia (fALU) Core_ _708-240-7598 - SKYPE Number_ _815-715-8048 - Cell Number_ - - - - This email and any files transmitted with it are AT&T property, are confidential, and are intended solely for the use of the individual or entity to whom this email is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender and delete this message immediately from your computer. Any other use, retention, dissemination, forwarding, printing or copying of this email is strictly prohibited. **From:** Aleksey Bazhenov [[mailto:bounce-AlekseyBazhenov@toadworld.com](mailto:bounce-AlekseyBazhenov@toadworld.com)] **Sent:** Wednesday, May 24, 2017 3:34 PM **To:** [toaddatapoint@toadworld.com](mailto:toaddatapoint@toadworld.com) **Subject:** RE: [Toad Data Point - Discussion Forum] failing query **[RE: failing query](https://urldefense.proofpoint.com/v2/url?u=http-3A__www.toadworld.com_products_toad-2Ddata-2Dpoint_f_36_p_32611_66021-2366021&d=DwMFaQ&c=LFYZ-o9_HUMeMTSQicvjIg&r=spEHEtGQcezYM0XDc_aOZg&m=qmLPBPRWhLtibKreZZsq0ZXrY4-Yut_UlUI2nSvNCxo&s=fJMs0-e1IzWUKuBy6VaKcjEkyLmUsjaoJrCKf9nBVD4&e=)** Reply by Aleksey Bazhenov Number of columns almost does not affect execution time but the number of rows that meets the filter does. It looks like Vertica query optimizer does not like more than 4 joins (5 tables) . I would suggest to change order of joins (you can use brackets to change the order) - join 3 table , join second 3 tables and now join these two joins. Or you can use subgueries like SELECT …. FROM subquery\_1 inner join subquery\_2 on … Subquery\_1 is join of the first 3 tables and subquery\_2 is the join of the rest. Aleksey To reply, please reply-all to this email. [Stop receiving emails](https://urldefense.proofpoint.com/v2/url?u=http-3A__www.toadworld.com_products_toad-2Ddata-2Dpoint_f_36_t_32611-3Fsubscribe-3Dfalse&d=DwMFaQ&c=LFYZ-o9_HUMeMTSQicvjIg&r=spEHEtGQcezYM0XDc_aOZg&m=qmLPBPRWhLtibKreZZsq0ZXrY4-Yut_UlUI2nSvNCxo&s=c2Bm9KMD6KhDmzx47F5sbch24ipQDHCeWXi6PBOrBg8&e=) on this subject. Or [ Unsubscribe from Toad Data Point Forum](https://urldefense.proofpoint.com/v2/url?u=http-3A__www.toadworld.com_products_toad-2Ddata-2Dpoint_f_36-3Fsubscribe-3Dfalse&d=DwMFaQ&c=LFYZ-o9_HUMeMTSQicvjIg&r=spEHEtGQcezYM0XDc_aOZg&m=qmLPBPRWhLtibKreZZsq0ZXrY4-Yut_UlUI2nSvNCxo&s=QhFH3NVZxMRv7nyUl6TzgVbV13zhRFYo_3oZ7yjsq_g&e=) notifications altogether. Toad Data Point - Discussion Forum [Flag](https://urldefense.proofpoint.com/v2/url?u=http-3A__www.toadworld.com_products_toad-2Ddata-2Dpoint_f_36_p_32611_66021-3FAbuseContentId-3Dae9ac493-2Df702-2D43d5-2D9087-2D795538ec2e25-26AbuseContentTypeId-3Df586769b-2D0822-2D468a-2Db7f3-2Da94d480ed9b0-26AbuseFlag-3Dtrue&d=DwMFaQ&c=LFYZ-o9_HUMeMTSQicvjIg&r=spEHEtGQcezYM0XDc_aOZg&m=qmLPBPRWhLtibKreZZsq0ZXrY4-Yut_UlUI2nSvNCxo&s=QfAp_Gfc30SDQYtzqUdZUpIQf6mM7kHBMGRx9ZOTLjE&e=) this post as spam/abuse. ---
**Author:** ![mf1375](https://avatars.discourse-cdn.com/v4/letter/m/9de053/32.png) [@mf1375](https://forums.toadworld.com/u/mf1375)\ **Post date:** [May 26, 2017, 9:30am UTC](https://forums.toadworld.com/t/failing-query/39502/17 "2017-05-26T09:30:42Z")
This was a great help to get to the correct formatting. As always, cross the “t” and dot the “I”. _Mark Fringer_ _AT&T Network Operations_ _Operational Certification and Advanced Technical Support (OCATS)_ _Nokia (fALU) Core_ _708-240-7598 - SKYPE Number_ _815-715-8048 - Cell Number_ - - - - This email and any files transmitted with it are AT&T property, are confidential, and are intended solely for the use of the individual or entity to whom this email is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender and delete this message immediately from your computer. Any other use, retention, dissemination, forwarding, printing or copying of this email is strictly prohibited. **From:** Aleksey Bazhenov [[mailto:bounce-AlekseyBazhenov@toadworld.com](mailto:bounce-AlekseyBazhenov@toadworld.com)] **Sent:** Thursday, May 25, 2017 12:15 PM **To:** [toaddatapoint@toadworld.com](mailto:toaddatapoint@toadworld.com) **Subject:** RE: [Toad Data Point - Discussion Forum] failing query **[RE: failing query](https://urldefense.proofpoint.com/v2/url?u=http-3A__www.toadworld.com_products_toad-2Ddata-2Dpoint_f_36_p_32611_66032-2366032&d=DwMFaQ&c=LFYZ-o9_HUMeMTSQicvjIg&r=spEHEtGQcezYM0XDc_aOZg&m=_qOQsZ6AiIyRWvtcFjgWeRzrQnxUh1DuA6iyEXxAKTA&s=xgD71nL6ywgvrc0aT8IemKQSIoOG1sD8K2ahTC5OanU&e=)** Reply by Aleksey Bazhenov It should be something likes the following. Of course, you have to put columns you need and make sure that the subquery\_1 and subquery\_2 can be executed. SELECT all\_columns\_you\_need FROM ( SELECT MME\_MAF.datetime as datetime, ``` MME_MAF.MME as MME, _other_ columns_you_need_1 ``` FROM ``` ((PDW_ALUMME_VIEWS.MME_MAF MME_MAF INNER JOIN PDW_ALUMME_VIEWS.MME_MAF2 MME_MAF2 ON (MME_MAF.datetime = MME_MAF2.DATETIME) AND (MME_MAF.mme = MME_MAF2.MME)) INNER JOIN PDW_ALUMME_VIEWS.MME_MAF_1 MME_MAF_1 ON (MME_MAF.datetime = MME_MAF_1.DATETIME) AND (MME_MAF.mme = MME_MAF_1.MME)) ) subquery_1 ``` INNER JOIN ( SELECT MME\_MAF\_2\_1.datetime as datetime, MME\_MAF\_2\_1.MME as MME, _other_ columns\_you\_need\_2 FROM PDW\_ALUMME\_VIEWS.MME\_MAF\_2\_1 MME\_MAF\_2\_1 ``` INNER JOIN PDW_ALUMME_VIEWS.MME_MAF1 MME_MAF1 ON (MME_MAF_2_1.datetime = MME_MAF1.DATETIME) AND (MME_MAF_2_1.MME = MME_MAF1.MME)) INNER JOIN PDW_ALUMME_VIEWS.MME_MAF_1_1 MME_MAF_1_1 ON (MME_MAF_2_1.datetime = MME_MAF_1_1.DATETIME) AND (MME_MAF_2_1.mme = MME_MAF_1_1.MME) ``` ) subquery\_2 ON ON (subquery\_1.datetime = subquery\_2.DATETIME) AND (subquery\_1.mme = subquery\_2.MME)) To reply, please reply-all to this email. [Stop receiving emails](https://urldefense.proofpoint.com/v2/url?u=http-3A__www.toadworld.com_products_toad-2Ddata-2Dpoint_f_36_t_32611-3Fsubscribe-3Dfalse&d=DwMFaQ&c=LFYZ-o9_HUMeMTSQicvjIg&r=spEHEtGQcezYM0XDc_aOZg&m=_qOQsZ6AiIyRWvtcFjgWeRzrQnxUh1DuA6iyEXxAKTA&s=zcqroew7LczBv_NR0CwegyDUJmth5T5SN6Eadd8mpRU&e=) on this subject. Or [ Unsubscribe from Toad Data Point Forum](https://urldefense.proofpoint.com/v2/url?u=http-3A__www.toadworld.com_products_toad-2Ddata-2Dpoint_f_36-3Fsubscribe-3Dfalse&d=DwMFaQ&c=LFYZ-o9_HUMeMTSQicvjIg&r=spEHEtGQcezYM0XDc_aOZg&m=_qOQsZ6AiIyRWvtcFjgWeRzrQnxUh1DuA6iyEXxAKTA&s=W312NXxK50pUa_0pIa4zOVhjBN3lCwy0U-N-sccB8Mg&e=) notifications altogether. Toad Data Point - Discussion Forum [Flag](https://urldefense.proofpoint.com/v2/url?u=http-3A__www.toadworld.com_products_toad-2Ddata-2Dpoint_f_36_p_32611_66032-3FAbuseContentId-3Db867deb9-2D7584-2D46f9-2D857f-2Dae30949be88c-26AbuseContentTypeId-3Df586769b-2D0822-2D468a-2Db7f3-2Da94d480ed9b0-26AbuseFlag-3Dtrue&d=DwMFaQ&c=LFYZ-o9_HUMeMTSQicvjIg&r=spEHEtGQcezYM0XDc_aOZg&m=_qOQsZ6AiIyRWvtcFjgWeRzrQnxUh1DuA6iyEXxAKTA&s=mRQa6lRTWzsU3Zk_yqlePD0S3KbNG0SHsMbkodC1Fw4&e=) this post as spam/abuse.