Have you ever had to edit SQL that was pieced together dynamically that your debugger
says looks like this:
insert
into table1 (col1,col2,col3,col4,col5,col6,col7)SELECT price.col1 , price.col2 , price.col3
, max(price.col4) AS col4, max(price.col5) AS col5, max(price.col6) AS col6, max(price.col7)
AS col7 /* comment2 */ FROM ( SELECT store.column1, cast (store.column2 AS integer)
AS column2, store.column3, store.column4, store.column5, substr(store.column6,11,1)
AS column6, store.column7 AS column7 FROM ( SELECT library.column1, library.column2,
library.column3, CASE library.column4 WHEN cheap THEN digits(library.column27) concat
library.column28 ELSE 123456 END AS column4, CASE library.column5 WHEN expensive THEN
digits(library.column27) concat library.column28 ELSE 123456 END AS library.column6,
CASE column7 WHEN free THEN digits(library.column27) concat library.column28 ELSE
123456 END AS column7, FROM ( SELECT integer(substr(onelibrarysales.column1,11,10))
AS column1, substr(onelibrarysales.column2,21,10) AS column2 , onelibrarysales.column3,
onelibrarysales.column4, substr(onelibrarysales.column5,31,6) AS column5, substr(onelibrarysales.column6,37,2)
AS column6, substr(onelibrarysales.column7,39,6) AS column7, FROM ( SELECT alllibrarysales.column1,
alllibrarysales.column2, max(alllibrarysales.column3) AS alllibrarysales.column3
I know that you have. God knows *I* have. Today, thanks to he
that shall not be named over at the JOS boards, I have found a site (ok,
I clicked a link. Hey - if Columbus can “discover” America...) that
will take the above monstrousity and turn it into yummy SQL goodness:
INSERT
INTO table1
(
col1,col2,col3,col4,col5,col6,col7
)
SELECT
price.col1 , price.col2 , price.col3 , max(price.col4) AS
col4, max(price.col5) AS col5, max(price.col6) AS col6, -- comment1
max(price.col7) AS col7 /* comment2 */
FROM
(
SELECT
store.column1,
-- =========================================
-- Thank you for trying SQLFormatter
-- =========================================
cast (store.column2 AS integer) AS column2, store.column3, store.column4,
store.column5, substr(store.column6,11,1) AS column6, store.column7 AS
column7
FROM
(
SELECT
library.column1, library.column2, library.column3,
CASE library.column4
WHEN cheap
THEN digits(library.column27) concat library.column28
ELSE 123456
END AS column4,
CASE library.column5
WHEN expensive
THEN digits(library.column27) concat library.column28
ELSE 123456
END AS library.column6,
CASE column7
WHEN free
THEN digits(library.column27) concat library.column28
ELSE 123456
END AS column7,
FROM
(
SELECT
integer(substr(onelibrarysales.column1,11,10))
AS column1, substr(onelibrarysales.column2,21,10) AS column2 , onelibrarysales.column3,
onelibrarysales.column4, substr(onelibrarysales.column5,31,6) AS column5, substr(onelibrarysales.column6,37,2)
AS column6, substr(onelibrarysales.column7,39,6) AS column7,
FROM
(
SELECT
alllibrarysales.column1, alllibrarysales.column2,
max(alllibrarysales.column3) AS alllibrarysales.column3 , max(char(alllibrarysales.column4,iso)
concat char(alllibrarysales.column5,iso) concat digits(alllibrarysales.column6) concat
(alllibrarysales.column7)) AS column5
FROM
(
SELECT
libraryprod.column1,
libraryprod.column2, libraryprod.column3, libraryprod.column4, libraryprod.column5,
libraryprod.column6, libraryprod.column7
FROM
(
SELECT
tv.column1,
tv.column2, max(digits(tv.column3) concat digits(tv.column4) ) AS librarymax
FROM
db1.v_table1 tv
WHERE tv.column1
<> 'Y'
AND
tv.column1 in ( 'a','b','c')
AND
tv.column2 >= date(tv.column4)
AND
tv.column3 < date(tv.column15)
GROUP BY tv.column1,
tv.column2
) AS libraryprod,
db1.table2 th
WHERE th.column1 =libraryprod.column1
AND th.column2
=libraryprod.column2
) AS alllibrarysales
GROUP BY alllibrarysales.column1, alllibrarysales.column2
) AS onelibrarysales
) AS library
LEFT OUTER JOIN
db1.v_table3 librarystat
ON librarystat.column1 = library.column1
AND librarystat.column2 = library.column2
OR
(
librarystat.column4 = library.column4
AND librarystat.column5 = library.column5
)
AND
(
librarystat.column5 = 'I'
OR librarystat.column4 = 'Gold'
OR librarystat.column5 = 'Bold'
)
AND librarystat.column6 <= 'Z74'
) AS x
) AS price
WHERE price.column1 < 'R45'
OR
(
price.column2= 'R46'
AND price.column3 = 6
)
GROUP BY price.column1, price.column2, price.column3, price.column4, price.column5,
price.column6, price.column7
Well, maybe not goodness, this was just a sample it produced - but it's definitely
a heck of a lot easier to read. Oh yeah, no download, no nothing - it's just
a Java applet, totally free (but not Free). Head over to SQLInform.com,
hit CTRL-D, and never deal with rewriting SQL to be pretty again.
- G
This
work is licensed under a
Creative
Commons License.