Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
Which is not the answer to the question - does anybody know of a tool for prettying up SQL code? Correcting the problem at source is not going to correct the current mess.
/*-------------------------------------------------------------------*/
/* Process the file and break up the lines */
/*-------------------------------------------------------------------*/
Address ISREDIT
"MACRO"
"UNNUM"
"HILITE ON OTHER PAREN "
/* "PASTE ADBISQLV DELETE" */
/*-------------------------------------------------------------------*/
/* Get all of the lines and compose single long string for */
/* ease of parsing */
/*-------------------------------------------------------------------*/
"cursor = .zlast"
"(row) = CURSOR"
alllines = ' '
Do x = 1 to row
"(aline) = LINE" x
/* If NUM ON, ignore numbers */
If datatype(right(aline,8),'N') Then
parse var aline aline 73 .
alllines = alllines || strip(aline) || ' '
End
"DELETE ALL NX"
/*-------------------------------------------------------------------*/
/* Change single quote to '~' to simplify subsequent changes */
/*-------------------------------------------------------------------*/
alllines = translate(alllines,"~", "'")
workline = alllines
/*-------------------------------------------------------------------*/
/* Remove quoted string in working storage to avoid false positives */
/*-------------------------------------------------------------------*/
qt0 = 2
qt2 = 0
Do y = 1 To 10 While (qt0 > 1)
/* find a quote (left) */
qt1 = Pos("~",workline, qt0)
If qt1 > 0 Then
Do
/* find a quote (right) */
qt2 = Pos("~",workline, qt1+1)
/*-------------------------------------------------------------------*/
/* determine parenthesis level for each paren in the SQL */
/*-------------------------------------------------------------------*/
parens = copies(' ',length(workline))
plevel = 0
Do z = 1 to length(workline)
char1 = substr(workline,z,1)
Select
When char1 = '(' Then
Do
plevel = plevel + 1
parens = OVERLAY(plevel, parens, z, 1)
say z '= <'char1'>' plevel
End
When char1 = ')' Then
Do
parens = OVERLAY(plevel, parens, z, 1)
say z '= <'char1'>' plevel
plevel = plevel - 1
End
Otherwise
say z '= <'char1'>'
End
End
/*-------------------------------------------------------------------*/
/* Remove VALUE commas from working storage to void line breaks */
/*-------------------------------------------------------------------*/
strt = 2
valstr = 1
Do While (valstr > 0)
valstr = Pos('VALUE(', workline, strt)
If (valstr > 0) Then
Do
valst2 = valstr + length('VALUE')
plevel = substr(parens,valst2,1)
parend = Pos(plevel, parens, valst2+1)
worksub = substr(workline, valst2, parend-valst2+1)
commasub = 1
Do While (commasub > 0)
commasub = Pos(',', worksub)
If commasub > 0 Then
Do
commaline = commasub + valst2 - 1
worksub = Overlay(' ', worksub , commasub , 1)
workline = Overlay(' ', workline, commaline, 1)
End
End
strt = valstr + 2
say 'work<'workline'>'
End
End
/*-------------------------------------------------------------------*/
/* Remove TYPE values from working storage */
/*-------------------------------------------------------------------*/
Do ix = 1 To char.0
strt = 2
valstr = 1
Do While (valstr > 0)
valstr = Pos(char.ix, workline, strt)
If (valstr > 0) Then
Do
valstr = valstr + length(char.ix)
valend = Pos(')', workline, valstr)
workline = Overlay(' ', workline, valstr, valend-valstr)
strt = valend
End
End
End
/*-------------------------------------------------------------------*/
/* Find keywords and insert carriage return */
/*-------------------------------------------------------------------*/
Do ix = 1 To srch.0
strt = 2
fnd = 1
Do While (fnd > 0)
fnd = Pos(srch.ix, workline,strt)
If fnd > 0 Then
Do
brk = fnd -1
If ix > after Then
brk = fnd
workline = Insert('05'x || Left(' ',indent.ix) , workline , brk)
alllines = Insert('05'x || Left(' ',indent.ix) , alllines, brk)
strt = fnd + 2 + indent.ix
End
End
End
/*-------------------------------------------------------------------*/
/* break up the line and insert into edit space */
/*-------------------------------------------------------------------*/
therest = alllines
Do While (Length(therest) > 0)
Parse Var therest aline '05'x therest
Parse Var aline keywrd .
If keywrd = 'LEFT' |,
keywrd = 'WHERE' Then
"line_AFTER .zlast= <1 ' ' >" /* skip a line */
"line_AFTER .zlast= <1 '"aline"' >"
End
"CHANGE '~' '7D'x ALL"
"RESET"
/* "DEFINE END ALIAS CANCEL" */
It works on the whole file. If you have sql imbedded into another language, it will not work.
fyi. I had wanted to improve this and make it into a product, but it was a spare time project and I retired before finishing it.
