Toad World® Forums

failing query

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.

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]

Sent: Tuesday, May 23, 2017 9:04 AM

To: toaddatapoint@toadworld.com

Subject: [Toad Data Point - Discussion Forum] failing query

failing query

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 on this subject.

Or
Unsubscribe from Toad Data Point Forum
notifications altogether.

Toad Data Point - Discussion Forum

Flag
this post as spam/abuse.

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

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]

Sent: Tuesday, May 23, 2017 12:05 PM

To: toaddatapoint@toadworld.com

Subject: RE: [Toad Data Point - Discussion Forum] failing query

RE: failing query

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 on this subject.

Or
Unsubscribe from Toad Data Point Forum
notifications altogether.

Toad Data Point - Discussion Forum

Flag
this post as spam/abuse.

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

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

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]

Sent: Tuesday, May 23, 2017 1:44 PM

To: toaddatapoint@toadworld.com

Subject: RE: [Toad Data Point - Discussion Forum] failing query

RE: failing query

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 on this subject.

Or
Unsubscribe from Toad Data Point Forum
notifications altogether.

Toad Data Point - Discussion Forum

Flag
this post as spam/abuse.

Hi Mark,

I suggest to verify that the DATETIME and MME columns in all tables are indexed.

Regards

Aleksey

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]

Sent: Wednesday, May 24, 2017 1:35 PM

To: toaddatapoint@toadworld.com

Subject: RE: [Toad Data Point - Discussion Forum] failing query

RE: failing query

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 on this subject.

Or
Unsubscribe from Toad Data Point Forum
notifications altogether.

Toad Data Point - Discussion Forum

Flag
this post as spam/abuse.

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.

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

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]

Sent: Wednesday, May 24, 2017 3:56 PM

To: toaddatapoint@toadworld.com

Subject: RE: [Toad Data Point - Discussion Forum] failing query

RE: failing query

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 on this subject.

Or
Unsubscribe from Toad Data Point Forum
notifications altogether.

Toad Data Point - Discussion Forum

Flag
this post as spam/abuse.

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]

Sent: Wednesday, May 24, 2017 3:34 PM

To: toaddatapoint@toadworld.com

Subject: RE: [Toad Data Point - Discussion Forum] failing query

RE: failing query

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 on this subject.

Or
Unsubscribe from Toad Data Point Forum
notifications altogether.

Toad Data Point - Discussion Forum

Flag
this post as spam/abuse.

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))

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.

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]

Sent: Wednesday, May 24, 2017 3:34 PM

To: toaddatapoint@toadworld.com

Subject: RE: [Toad Data Point - Discussion Forum] failing query

RE: failing query

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 on this subject.

Or
Unsubscribe from Toad Data Point Forum
notifications altogether.

Toad Data Point - Discussion Forum

Flag
this post as spam/abuse.

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]

Sent: Thursday, May 25, 2017 12:15 PM

To: toaddatapoint@toadworld.com

Subject: RE: [Toad Data Point - Discussion Forum] failing query

RE: failing query

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 on this subject.

Or
Unsubscribe from Toad Data Point Forum
notifications altogether.

Toad Data Point - Discussion Forum

Flag
this post as spam/abuse.