Toad World® Forums

SQL formatter: How to align and remove excessive brackets

Ok. Here is problem:

I have this SQL…



FROM ((((((AimsDevelopment.dbo.tblStudentUnitRegister su
INNER JOIN AimsDevelopment.dbo.vwStudentProgramCurrent vwSPC
ON (su.StudentID = vwSPC.StudentID))
LEFT OUTER JOIN AimsDevelopment.dbo.tblProgramUnitRegister pu
ON (su.ProgramUnitID = pu.ProgramUnitID)
AND (su.UnitCode = pu.UnitCode)
AND (su.Stage = pu.StudyYearID))
INNER JOIN AimsDevelopment.dbo.tblStudents s
ON (s.StudentID = su.StudentID))
INNER JOIN AimsDevelopment.dbo.tblCampuses c
ON (s.StudentCampus = c.CampusID))
LEFT OUTER JOIN AimsDevelopment.dbo.tblUnitOutcomes uo
ON (su.OutcomeID = uo.UnitOutcomeID))
INNER JOIN AimsDevelopment.dbo.tblUnits u ON (su.UnitCode = u.UnitCode))
INNER JOIN AimsDevelopment.dbo.tblQualifications q
ON (su.QualificationCode = q.QualificationCode)

What I want is this:



FROM AimsDevelopment.dbo.tblStudentUnitRegister su
INNER JOIN AimsDevelopment.dbo.vwStudentProgramCurrent vwSPC
ON (su.StudentID = vwSPC.StudentID)
LEFT OUTER JOIN AimsDevelopment.dbo.tblProgramUnitRegister pu
ON (su.ProgramUnitID = pu.ProgramUnitID)
AND (su.UnitCode = pu.UnitCode)
AND (su.Stage = pu.StudyYearID)
INNER JOIN AimsDevelopment.dbo.tblStudents s
ON (s.StudentID = su.StudentID)
INNER JOIN AimsDevelopment.dbo.tblCampuses c
ON (s.StudentCampus = c.CampusID)
LEFT OUTER JOIN AimsDevelopment.dbo.tblUnitOutcomes uo
ON (su.OutcomeID = uo.UnitOutcomeID)
INNER JOIN AimsDevelopment.dbo.tblUnits u
ON (su.UnitCode = u.UnitCode)
INNER JOIN AimsDevelopment.dbo.tblQualifications q
ON (su.QualificationCode = q.QualificationCode)

Is there a way to do this?

Thank you

Hi,
Can you please add a bit more info on this?

Hi Alex,

What sort of details are you looking for?

It’s quite a simple request. Can TOAD be preferenced using the format options to do what I explained above? Instead of TOAD generating all those excessive brackets which look ugly IMO, is there a way to make it format the code as per my 2nd example?

Sorry, I really don’t know how much more simple do you want me to explain it.

Thanks

My question was how do you get this query in such a huge tool like Toad - manually write it in Editor with using Code Completion/generate DDL for your view/ from Query Builder any other script generation place?

Hi Alex,

Thank you for your time. My apologies. Yes I was not clear with how the code was first generated.

It was generated using the diagram feature of TOAD for SQLsrv 5.8.

So I simply dragged and dropped the tables into the Query Builder’s diagram screen and provided the links shown. TOAD automatically added in the brackets, as shown in the above 1st example.

Is there a way to make TOAD not add brackets over everything all the time? If not, then can it be told to align the tables similar or close to the 2nd example I provided above?

Thank you

The problem has been found before by the way with no results …

http://tda.inside.quest.com/thread.jspa?threadID=28879

Maybe you should speak with Debbie? She might know the answer. Thanks

Right, it is the same issue. As you can see the issue still not fixed since 2010. Seems it was decided to not fix this by some reason (work correct as a whole, doesn’t lead to any error).

Hi Alex,

Yes the SQL is synatically correct. So that’s it?

Can a developer in these forums please help?

Thanks

Dear Alex,

Could you please raise a ticket to have this resolved, as kindly as you and Julia did for SanskritFritz in this thread.

http://toadforsqlserver.com/thread.jspa?threadID=38618&start=0&tstart=0

Thank you

The reason why I am so pedantic about this change is because I have to share my SQL with non-SQL developers and other IT people in my team to explain to them logic used in our reports. This is why I loved TOAD for Data Analysts (now DataPoint). That tool did still put excessive brackets and bad alignment all over the place with the SQL, but obviously since I raised this with Debbie back 2010, nothing has come of it. Shame

Actually I did have my Query Builder developer look at it. We found it to be too high risk of a change to make. My error was not in letting you know at the time.

Sorry.

Debbie

Thanks for stepping in Debbie. Much appreciated.
“Answered”.

I have Toad Data Point vs 3.8.1.677.

Are you planning to NEVER fix this issue? I am a beginner SQL user and love TOAD because it is a great stepping stone for me (going from MS Access to SQL Server). However, I have to develop a lot of test queries then send them to seasoned SQL users for implementation. I have to manually clean each up each query before I send them.

This only happens when I use the ‘Visualize the sql statement…’ icon. Out of five of us using Toad, it only happens to two of us consistently.

Is there no hope for a fix in the future?

Deb

IMHO, and to be confirmed by the real experts, in this case (only!) the parentheses around the joins can be safely removed as joins are executed from the left to the right and the parenthesis go outwards.

But I’m wondering why nobody suggests to remove the superfluous parentheses in the ON clauses?

Let me just add that it is not the job of a code formatter to remove parentheses. A formatter should never change code. Changing the code with the purpose of simplification and alike is rather refactoring.