Formatter will not format code when pasted into an Editor from Visual Studio 2010

Toad Data Point & Toad Intelligence Central
1

I support a web application that contains SQL statements which are passed to a Teradata database. When I need to debug a SQL statement, I copy the statement from the variable and paste it into a Toad Data Point editor.

I can type a SQL statement into a Toad Data Point Editor and successfully format the statement, but when I paste a SQL statement that was copied from Visual Studio, the Formatter will not format the statement.

I do notice that when I paste from Visual Studio into the Editor, the Select clause is preceeded by a collapse/expand box (i.e a square w/ a dash inside of it), but I only see this when I paste code into the Editor from Visual Studio. I have copied/pasted SQL statements from Notepad into the Editor and the Formatter works as expected. I have even tried pasting code (copied from Visual Studio) into NotePad, then copying the code in NotePad and pasting it into the Editor, but the Formatter will not format the code.

This extremely frustrating, since most of the SQL statements are rather lengthy and it takes too much time to manually format the code in the Editor.

Would somebody be able to help me understand what is going on?

Thanks in advance!

Kevin

2

I am presuming you are using the format button after you paste in the SQL. If you click the drop down on it, you can see several formatting types below it. Try selecting one of those to see if it works:

format.png
format.png862×446 44.5 KB

If you still have problems, can you give an example of what you are pasting in?

3

Thanks for your reply! It appears that there may be a limit to the number of chars that can be formatted within the Toad editor. I can paste a string w/ approximately 1,300 chars and Toad will format it, but if I paste ~1,500 chars into the editor then Toad will not format the statement. I have tried pasting code in 1,000 char chunks, but when the total number of chars in the statement reaches ~1,500 chars then the formatter stops formatting.

Here is a sample SQL statement that is about 1,507 chars long (including spaces). This statement will not format in the Toad editor, but take a couple hundred chars off of the statement and then it will format.

SELECT CASE WHEN MBU = ‘’ THEN ‘JOE’ WHEN mbu = ‘SAM’ THEN ‘JO’ WHEN mbu = ‘JILL’ THEN ‘SALLY’ ELSE MBU END AS MBU, SUM(TotalDefects) AS TotalDefects, SUM(DefPerBike) AS DefPerBike, SUM(PYTotalDefects) AS PYTotalDefects, SUM(PYDefectsPerBike) AS PYDefPerBike FROM(SELECT CASE WHEN MBU = ‘’ THEN ‘JOE’ WHEN mbu = ‘SAM’ THEN ‘HARRY’ WHEN mbu = ‘JILL’ THEN ‘SALLY’ ELSE MBU END AS MBU, SUM(DefCount ) AS TotalDefects, SUM(DefectsPerBike) AS CYDefPerBike, CAST(0 AS NUMERIC(9,2)) AS PYTotalDefects, CAST(0 AS NUMERIC(9,3)) AS PYDefectsPerBike FROM (SELECT DISTINCT qf.MBU,abc,qf.wheel,CAST(EXTRACT(YEAR FROM countdate) AS INTEGER) AS YYYY, CAST(EXTRACT(MONTH FROM countdate) AS INTEGER) AS MM, Def_no, COALESCE(lineNumber,0) AS LineNumber, CAST(defect AS NUMERIC(9,2)) AS DefCount, CAST(defect/number AS NUMERIC(9,3)) AS DefectsPerBike FROM AFV.table_name QF INNER JOIN QAFT.table2 MR ON QF.MBU = MR.MBU AND MR.MBU_RU = ‘SAM’ INNER JOIN views.RATES_DATE B ON EXTRACT(YEAR FROM countdate) = EXTRACT(YEAR FROM b.CAL_DATE) AND EXTRACT(MONTH FROM countdate) = EXTRACT(MONTH FROM b.cal_date) AND qf.WHEEL = b.WHEEL AND b.Model IN ( ‘new’,‘old’,‘current’,‘future’,‘longterm’) WHERE qf.Model IN ( ‘new’,‘old’,‘current’,‘future’,‘longterm’) AND EXTRACT(YEAR FROM QF.CountDate) = 2014 AND QF.CountDate <= ‘04/08/2014’ (DATE, FORMAT ‘MM/DD/YYYY’) AND COALESCE(C_code,0) NOT IN (‘JOE’) UNION ALL SELECT DISTINCT QF.MBU_Resp AS MBU, FRAME, qf.model, CAST(EXTRACT(YEAR FROM countdate) AS INTEGER) AS YYYY,

4

I think I know what the issue may be; In order for something to be formatted, it must be a complete, correct sql statement that can be handled by the parser. In the above example, it seems to be cut off at some point, and I think there were a few syntax errors involving usage of reserved words(I am presuming that this is on Oracle?). What I think was happening when you were shaving off characters was that you were only removing the parts that were invalid. Try formatting a statement, but if it seems to not work, try looking at the Messages tab of the Results pane:

messages.png
messages.png1062×270 16.7 KB

Correct the syntax errors you see and try parsing again. Repeat until there are no more syntax errors and let me know if everything seems to format.

5

Ryan: Thanks for your reply. I know that the SQL statement provided is not syntactically correct, but Toad will format a SQL statement that is around 1,300 chars even if the syntax is incorrect. The statement I provided is about 1,500 chars in length and if you paste it into a Toad Editor (I’m using Toad Data Point v3.4), the SQL statement will paste as a single line. When you click the “Formatter” bucket, the SQL statement will not format and remain as a single line. If you remove a couple hundred characters and then click the “Formatter” button, then the SQL statement will format and Toad will throw a parser error. It appears that there is a limit to the number of characters that exist in the Toad Editor that Toad will format. I have a ticket in with Dell regarding this.