The attached module contains a function called FormatSelect, which formats an SQL Select statement string so that it is easier to read.
This should hopefully help you to make changes more easiy, or (if you have errors) enable you to see any mistakes much more quickly.
As a little bonus, the function will also notify you if there are obvious errors, such as the wrong number of quotes to mark the start/end of strings.
Example usage:
VB Code:
Dim sSQL as String
sSQL = "SELECT Table1.Field1 FROM Table1 WHERE Field2 = 3"
sSQL = FormatSelect(sSQL)
MsgBox sSQL
A couple of examples of the inputs/outputs of the function:
1) if the following Select statement is provided as input:
Code:
SELECT Table1.Field1 FROM Table1 WHERE Field2 = 3
...it gets converted into this:
Code:
SELECT Table1.Field1
FROM Table1
WHERE Field2 = 3
2) if this Select statement is provided as input:
Code:
SELECT Table1.Field1 AS Field1, Table1.Field2 AS Field2, CASE WHEN Table2.Field3 = 0 THEN 'NO'
WHEN Table2.Field3 = 1 THEN 'YES' WHEN Table2.Field3 IS NULL THEN 'NO' END CASE as Field3, IIf(Field4=1,1,0)
From Table1 LEFT JOIN Table2 ON Table1.Field1 = Table2.Field1 LEFT JOIN Table3 ON Table1.Field1 = Table3.Field1
Where Table1.Field5 Between '1/1/2005' AND '1/20/2005' AND Table1.Field6 Between '1/1/2005' AND '1/20/2005'
OR Table1.Field7 = '1/20/2005'
AND Table1.Field8 IN(SELECT Field1 FROM table4 WHERE field2 IN(SELECT Field1 WHERE a=b FROM table2))
...it gets converted into this:
Code:
SELECT Table1.Field1 AS Field1,
Table1.Field2 AS Field2,
CASE WHEN Table2.Field3 = 0 THEN 'NO' WHEN Table2.Field3 = 1 THEN 'YES' WHEN Table2.Field3 IS NULL THEN 'NO' END CASE as Field3,
IIf ( Field4 = 1, 1, 0 )
FROM Table1
LEFT JOIN Table2 ON Table1.Field1 = Table2.Field1
LEFT JOIN Table3 ON Table1.Field1 = Table3.Field1
WHERE Table1.Field5 Between '1/1/2005' AND '1/20/2005'
AND Table1.Field6 Between '1/1/2005' AND '1/20/2005'
OR Table1.Field7 = '1/20/2005'
AND Table1.Field8 IN (
SELECT Field1
FROM table4
WHERE field2 IN (
SELECT Field1
WHERE a = b
FROM table2
)
...with the following warning message:
Code:
(in sub-query:
(in sub-query:
Incorrect section ordering: FROM should be before WHERE
)
)
Note that the blank lines are optional, and can be disabled like this:
VB Code:
sSQL = FormatSelect(sSQL, False)
The number of spaces to use as an indent (for 'normal' text, and for subqueries) can also be specified, using two further optional parameters. eg:
Hi si_the_geek,
First off... I've been looking for something like this for a long time because in my application I made for work I have over 100 SQL statements that are like reports.
All the SQL statements are very complex, so I guess it's perfect for testing your code.
First off, I tried with 3 SQL statements until now, and 2 of them I get an infinite loop in this function: FormatSelect_Worker
In this code:
VB Code:
Select Case aKeywords(lThisSectionType)
Case "SELECT", "GROUP BY", "ORDER BY"
lTempPos = lCurrPos
Do
Do
lTempPos = InStr(lTempPos, sText, ",")
lBracketStart = InStr(lCurrPos, sText, "(")
If (lBracketStart > 0) And (lBracketStart < lTempPos) Then
This is one of the simple SQL statements it's doing this:
Code:
SELECT FORMAT(abt.DateChanged, "MM/DD/YYYY") AS "Date", aa.Action, Count(*) AS "Total"
FROM tblAbnormalTran abt, tblAbnormalTranActions aa
WHERE abt.Action = aa.[ID]
AND CDATE(FORMAT(abt.DateChanged, "MM/DD/YYYY"))
BETWEEN a AND b
GROUP BY FORMAT(abt.DateChanged, "MM/DD/YYYY"), aa.Action
ORDER BY FORMAT(abt.DateChanged, "MM/DD/YYYY") DESC
Last edited by CVMichael; Nov 8th, 2005 at 06:30 PM.
Reason: Some spelling mistakes...
OK... weird things are hapening.... I will highlight in bold those weird things:
Original SQL
Code:
SELECT FORMAT(fo.FileDate, "MM/DD/YYYY") AS "File Date",
Count(*) AS KITs, pa.Parts
FROM (tblOrderFileList ofl INNER JOIN tblOrderFile fo ON fo.OrderFileID = ofl.OrderFileID) LEFT JOIN
(
SELECT CDate(FORMAT(fo.FileDate, "MM/DD/YYYY")) AS FileDate,
OrderInstructionCode AS "Type",
Count(*) AS Parts
FROM tblOrderFileList ofl INNER JOIN tblOrderFile fo ON fo.OrderFileID = ofl.OrderFileID
WHERE fo.CompanyName = 'ABC'
AND fo.FileDate BETWEEN a AND b
AND OrderInstructionCode = "PART"
GROUP BY FORMAT(fo.FileDate, "MM/DD/YYYY"), OrderInstructionCode
) AS pa ON pa.FileDate = CDate(FORMAT(fo.FileDate, "MM/DD/YYYY"))
WHERE fo.CompanyName = 'ABC'
AND fo.FileDate BETWEEN INSERT_VALUE_1 AND INSERT_VALUE_2 + 1
AND OrderInstructionCode = "KIT"
GROUP BY FORMAT(fo.FileDate, "MM/DD/YYYY"), OrderInstructionCode, pa.Parts
ORDER BY CDate(FORMAT(fo.FileDate, "MM/DD/YYYY")) DESC
Formated SQL:
Code:
SELECT
FORMAT ( fo.FileDate, "MM/DD/YYYY" ) AS "File Date",
Count ( * ) AS KITs,
pa.Parts
FROM
( tblOrderFileList ofl INNER
JOIN tblOrderFile fo ON fo.OrderFileID = ofl.OrderFileID )
LEFT JOIN (
SELECT
CDate ( FORMAT ( fo.FileDate, "" ) ) AS FileDate,
OrderInstructionCode AS "",
Count ( * ) AS Parts
FROM
tblOrderFileList ofl INNER
JOIN tblOrderFile fo ON fo.OrderFileID = ofl.OrderFileID
WHERE
fo.CompanyName = ''
AND fo.FileDate BETWEEN a AND b
AND OrderInstructionCode = ""
GROUP BY
FORMAT ( fo.FileDate, "" ),
OrderInstructionCode
) AS pa ON pa.FileDate = CDate ( FORMAT ( fo.FileDate, "MM/DD/YYYY" ) )
WHERE
fo.CompanyName = 'ABC'
AND fo.FileDate BETWEEN INSERT_VALUE_1 AND INSERT_VALUE_2 + 1
AND OrderInstructionCode = "Type"
GROUP BY
FORMAT ( fo.FileDate, "PART" ),
OrderInstructionCode,
pa.Parts
ORDER BY
CDate ( FORMAT ( fo.FileDate, "MM/DD/YYYY" ) ) DESC
It's a problem with the quotes, it's removing stuff in the quotes, or replacing with wrong things...
SELECT FORMAT(ShipDate, "YYYY MM") AS [Month], Count(*) AS Total
FROM tblInvoiceOrderFileList
WHERE OrderClaimStatusCode = 4 AND ShipDate
BETWEEN a AND b
GROUP BY FORMAT(ShipDate, "YYYY MM")
ORDER BY FORMAT(ShipDate, "YYYY MM") DESC
Formated SQL:
Code:
SELECT
FORMAT ( ShipDate, "YYYY MM" ) AS [Month],
Count ( * ) AS Total
FROM
tblInvoiceOrderFileList
WHERE
OR derClaimStatusCode = 4
AND ShipDate BETWEEN a AND b
GROUP BY
FORMAT ( ShipDate, "YYYY MM" )
ORDER BY
FORMAT ( ShipDate, "YYYY MM" ) DESC
I think you have to make it search for "OR " (with space after) because some words (like the previous) might contain or...
[Edit]
PS, I made my own SQL formater, should I post it here, or should I make a new thread ?
Fixed, I had a ">" where I should have had a ">="!
it checks for any of these characters: " '""#()=<>[]+-/*" (or end of string) before & after strings to find, but missed the 'after' check when the match was the first character.
PS, I made my own SQL formater, should I post it here, or should I make a new thread ?
It's probably best to start a new thread (as I'm sure there'll be discussions for both), but add a link to the thread here - I'll add a link in the top post too.
It's probably best to start a new thread (as I'm sure there'll be discussions for both), but add a link to the thread here - I'll add a link in the top post too.
OK, I'm gonna post it soon, I just want to do a little more testing...
I found another problem with your SQL formatter:
I get an error at this line (in FindFirstByArray function):
lTempPos is = 1, so the result is 1 - 1 = 0, therefore an invalid # for the MID
And if I take off the -1, then the formatter says the select query is wrong on all my selects...
[Edit]
Also, something I forgot to say, Your formatter formats the code like this:
Code:
SELECT
Field1,
Field2,
....
When it's putting the "Field1" (the first field) on the second line, and I run the query, for some reason it does not return any data. But when I change it to this:
Code:
SELECT Field1,
Field2,
.....
Then everything is OK... it executes properly, and returns data...
Last edited by CVMichael; Nov 11th, 2005 at 12:23 PM.
Well I finally got my head into a coding mood again - I really don't know what was wrong with me!
Here's the updates:
Corrected FindFirstByArray.
Due to the problem mentioned above (SQL will not run in ?Access?), I removed the new line immediately after "Select"/"From"/etc, and added the option for blank lines between each 'section' instead.
Fixed typo for bracket errors.
Corrected mis-use of a variable for passing the And in a Between clause (could cause infinite loop if Between was malformed!).
The new version is MUCH better ! No errors at all !
But could you make it have an option for how many spaces to put for indent ?
Because 10 spaces is WAY too much for me, I like 4 spaces...
To give you an idea, this:
Code:
SELECT DISTINCT MFrom AS "From Model", MWith AS "To Model" FROM
(
SELECT DISTINCT MFrom, TRIM(IIF(MWith LIKE 'abc5%', MID(MWith, 5, 100), MWith)) AS MWith FROM
(
SELECT MFrom, IIF(INSTR(MWith, '(') = 0, MWith, LEFT(MWith, INSTR(MWith, '(')-1)) AS MWith FROM
(
SELECT DISTINCT MFrom, MWith FROM
(
SELECT MFrom, TRIM(IIF(Num3 = 0, MWith, LEFT(MWith, Num3 - 1))) AS MWith FROM
(
SELECT MFrom, MWith, IIF(Num3 = 0, Num2, Num3) AS Num3 FROM
(
SELECT MFrom, MWith, IIF(Num2 = 0, Num1, Num2) AS Num2, IIF(Num2 = 0, 0, INSTR(Num2 + 1, MWith, '-')) AS Num3
FROM
(
SELECT MFrom, MWith, Num1, IIF(Num1 = 0, 0, INSTR(Num1 + 1, MWith, '-')) AS Num2 FROM
(
SELECT MFrom, MWith, INSTR(MWith, '-') AS Num1 FROM
(
SELECT LEFT(m_ofl.Notes, INSTR(m_ofl.Notes, '|') - 1) AS MFrom
, IIF(INSTR(repl.Notes, ',') > 0, LEFT(repl.Notes, INSTR(repl.Notes, ',')-1), repl.Notes) AS MWith
FROM tblInvoice AS repl INNER JOIN
(
SELECT OrderNumber, Notes FROM tblInvoice
WHERE StatusCode = 4
AND INSTR(Notes, '|') > 0
) AS m_ofl ON m_ofl.OrderNumber = repl.OrderNumber
WHERE repl.RecordIDType = 'STAT'
AND repl.StatusCode = 7
AND repl.Notes NOT LIKE '%abc%'
AND repl.Notes NOT LIKE '%abc2%'
AND repl.Notes NOT LIKE '%abc3%'
AND ShipDate > #Apr 01, 2004#
))))))
WHERE LEN(MWith) > 1
)
WHERE INSTR(MWith, MFrom) = 0
))
ORDER BY MFrom, MWith
You get:
Code:
SELECT DISTINCT MFrom AS "From Model",
MWith AS "To Model"
FROM (
SELECT DISTINCT MFrom,
TRIM ( IIF ( MWith LIKE 'abc5%', MID ( MWith, 5, 100 ), MWith ) ) AS MWith
FROM (
SELECT MFrom,
IIF ( INSTR ( MWith, '(' ) = 0, MWith, LEFT ( MWith, INSTR ( MWith, '(' ) - 1 ) ) AS MWith
FROM (
SELECT DISTINCT MFrom,
MWith
FROM (
SELECT MFrom,
TRIM ( IIF ( Num3 = 0, MWith, LEFT ( MWith, Num3 - 1 ) ) ) AS MWith
FROM (
SELECT MFrom,
MWith,
IIF ( Num3 = 0, Num2, Num3 ) AS Num3
FROM (
SELECT MFrom,
MWith,
IIF ( Num2 = 0, Num1, Num2 ) AS Num2,
IIF ( Num2 = 0, 0, INSTR ( Num2 + 1, MWith, '-' ) ) AS Num3
FROM (
SELECT MFrom,
MWith,
Num1,
IIF ( Num1 = 0, 0, INSTR ( Num1 + 1, MWith, '-' ) ) AS Num2
FROM (
SELECT MFrom,
MWith,
INSTR ( MWith, '-' ) AS Num1
FROM (
SELECT LEFT ( m_ofl.Notes, INSTR ( m_ofl.Notes, '|' ) - 1 ) AS MFrom,
IIF ( INSTR ( repl.Notes, ',' ) > 0, LEFT ( repl.Notes, INSTR ( repl.Notes, ',' ) - 1 ), repl.Notes ) AS MWith
FROM tblInvoice AS repl
INNER JOIN (
SELECT OrderNumber,
Notes
FROM tblInvoice
WHERE StatusCode = 4
AND INSTR ( Notes, '|' ) > 0
) AS m_ofl ON m_ofl.OrderNumber = repl.OrderNumber
WHERE repl.RecordIDType = 'STAT'
AND repl.StatusCode = 7
AND repl.Notes NOT LIKE '%abc%'
AND repl.Notes NOT LIKE '%abc2%'
AND repl.Notes NOT LIKE '%abc3%'
AND
(I could not paste all the code, too many characters)
Wich is way to much indenting for me....
Also, where whould I have to change your code to make it act as if {} brackets are like a string ? I don't want your formatter to make any changes between the {} brackets, so how do I do that ?
I disagree with your spacing amounts, but agree with making it optional I have done that, and changed the default subquery indent size to be just over the normal indent, rather than double it.
To make {} act as a string, the best course of action would probably be to add an extra bit of code before/after the strings are removed to store the {} 'strings' to a separate array, then re-insert them after/before the strings (opposite of what you did above).
Unfortunately the strings code can't really be altered for this easily enough, as all other markers are the same for start/end, whereas these differ - a modified version ofthe strings code would work well tho. On the plus side, for this you would only need a one dimensional array - as you know the markers.
Are you still working on this formatting project? I am working on VB.Net project which can use your formatting function. My project basically allow in place formatting in SQL QUERY ANALYZER using Hooks.
I haven't done anything more to it yet, the trouble is that there are many different formats for Inserts (for example) depending on what DBMS you are using. I may have a go at adding more this weekend, assuming I am up to date on my work!
Your use of it sounds good, I'd like to see that when you get it working.
I originally designed it because we had lots of threads along the lines of "what is the error in this SQL statement?", and they didn't have any formatting at all (just one long string!).
That meant we had to manually format the SQL statements before we could attempt to answer. I realised that the amount of time I would save by having the code offset the time to write it.. and thankfully it was easy to add basic error checking too.
It also allows for those times when you are given an existing project to support, and the SQL statements aren't formatted (from my experience, this is often the case!).
Thanks for your comments... I can confirm that it doesn't handle Unions at the moment (I'm surprised nobody has mentioned it before!), and I will try to add that in the next few days.
This project was incredibly helpful to me, so I figured I would post my changes to make this VB.NET compliant. Nothing else seemed to be out there and completely free.
You should be able to add the .bas file by right-clicking in the Project Explorer window (or whatever it is called where you can see the code files), make sure you right-click on a folder rather than a file.
If that doesn't work just create a new module, then use Notepad to copy the text of the .bas file (except the first line), and paste the text into the new module.
You can then use it as shown earlier in this thread.
You should be able to add the .bas file by right-clicking in the Project Explorer window (or whatever it is called where you can see the code files), make sure you right-click on a folder rather than a file.
If that doesn't work just create a new module, then use Notepad to copy the text of the .bas file (except the first line), and paste the text into the new module.
You can then use it as shown earlier in this thread.
Alright thanks I think the best thing for me to do woudl be to go through some tutorials and try to figure out how I can write something to implement this.