Version 12 Query Builder - Aggregate Function Caution

Toad for Oracle
1

I recently upgraded my Toad for Oracle from free version to 12 and ran into an issue with the Query Builder. Examine the following sql assembled by QB:

SELECT FIELD1, COUNT( FIELD2) “CNT”

FROM MYTABLE

GROUP BY FIELD1

ORDER BY FIELD1

The results of this query looked something like:

VALUE1 5

VALUE1 3

VALUE1 2

VALUE2 3

VALUE2 6

Where the results should have appeared as:

VALUE1 10

VALUE2 9

The issue turned out to be the space between the “COUNT(” and the “FIELD2)”. Changing COUNT( FIELD2) to COUNT(FIELD2) resolved the issue. When I copied the SQL over to Editor it worked fine, even with the space. When I sent the Editor over to Query Builder (without the space), Query Builder again added the space that I had to remove. The space does not appear in the Query Browser window (tree) but shows up in the Generated Query window.

I did not find any configurable option that might be influencing this behavior. The closest I could find is in the Formatter options where I have Parentheses set to compact (no spaces).

2

I have found this issue to be slightly more than a minor annoyance. When you correct the COUNT( FIELD2) in the Generated Query window, the query runs fine. Unfortunately, if you make any modifications to your query in the graphical window, the updates to the Generated Query don’t reflect the changes without hitting the Update SQL. When you hit the Update SQL, the space is back. This means that every change you make to the query means that you need to Update SQL and correct the space before executing again. I certainly hope that I am overlooking a setting…

3

Hi DaddyDT,

I’m not able to reproduce this issue in Toad 12, 11.6, or 12.1; however, I suspect it may still be an issue with one of your Formatter settings in Toad. You
mentioned you had your Parentheses settings set to “Compact” within your Formatter options. That setting should prevent extraneous spaces to appear in your formatted SQL.

If you copy the SQL to the Editor, highlight it, and select “Formatting Tools -> Format” from the context menu, does it also include the extra space there?
If not, can you send me a copy of your Support Bundle offline to my e-mail address? We can attempt to track it down further from there.

Thanks!

-John

From: DaddyDT [mailto:bounce-DaddyDT@toadworld.com]

Sent: Tuesday, November 19, 2013 12:53 PM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Version 12 Query Builder - Aggregate Function Caution

RE: Version 12 Query Builder - Aggregate Function Caution

Reply by DaddyDT

I have found this issue to be slightly more than a minor annoyance. When you correct the COUNT( FIELD2) in the Generated Query window, the query runs fine. Unfortunately, if you make any modifications
to your query in the graphical window, the updates to the Generated Query don’t reflect the changes without hitting the Update SQL. When you hit the Update SQL, the space is back. This means that every change you make to the query means that you need to
Update SQL and correct the space before executing again. I certainly hope that I am overlooking a setting…

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

4

I am running version 12.0.0.61. Under View/Toad Options/Formatter/Operations & Punctuation/Parentheses the spacing is set to Compact. In both Query Builder and in Editor (after hitting Format) the space is inserted after the left parentheses. Under Formatter/List Arrangements the GROUP BY list is set to Default. In the Editor, if I remove the space the Formatter automatically puts it back...

I don't know if updating to 12.1 would help but since this is a site license I don't know that I am able to without the aid of submitting and IT request through our corporate processes.

Thanks,

David

On Tue, Nov 19, 2013 at 12:00 PM, John Bowman bounce-jbowman@toadworld.com wrote:

RE: Version 12 Query Builder - Aggregate Function Caution

Reply by John Bowman
Hi DaddyDT,

I’m not able to reproduce this issue in Toad 12, 11.6, or 12.1; however, I suspect it may still be an issue with one of your Formatter settings in Toad. You
mentioned you had your Parentheses settings set to “Compact” within your Formatter options. That setting should prevent extraneous spaces to appear in your formatted SQL.

If you copy the SQL to the Editor, highlight it, and select “Formatting Tools -> Format” from the context menu, does it also include the extra space there?
If not, can you send me a copy of your Support Bundle offline to my e-mail address? We can attempt to track it down further from there.

Thanks!

-John

From: DaddyDT [mailto:bounce-DaddyDT@toadworld.com]

Sent: Tuesday, November 19, 2013 12:53 PM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Version 12 Query Builder - Aggregate Function Caution

RE: Version 12 Query Builder - Aggregate Function Caution

Reply by DaddyDT

I have found this issue to be slightly more than a minor annoyance. When you correct the COUNT( FIELD2) in the Generated Query window, the query runs fine. Unfortunately, if you make any modifications
to your query in the graphical window, the updates to the Generated Query don't reflect the changes without hitting the Update SQL. When you hit the Update SQL, the space is back. This means that every change you make to the query means that you need to
Update SQL and correct the space before executing again. I certainly hope that I am overlooking a setting...

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.