|
View previous topic :: View next topic
|
| Author |
Message |
jzhardy
Active User
Joined: 31 Oct 2006
Posts: 150
Location: brisbane
|
|
|
|
to save reinventing the wheel, does anyone have, or know of, any REXX module that converts a string into formatted text.
eg, if src = 'select * from sysibm.sysdummy1 where 1=1 and (2=2 or 3=3)', then output (call it outf.) might be something like:
| Code: |
select *
from
sysibm.sysdummy1
where
1=1
or
(2=2 or 3=3)
|
|
|
| Back to top |
|
 |
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2286
Location: USA
|
|
|
|
I tried to expose the real, fundamental problem causing this issue, but my post has been silently removed by moderators.
So far this issue is equivalent to:
| Quote: |
| Is there any tool or any way to fix the problem, when my house has been built topsy-turvy? |
|
|
| Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2454
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. |
|
| Back to top |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1442
Location: Bamberg, Germany
|
|
| Back to top |
|
|
jzhardy
Active User
Joined: 31 Oct 2006
Posts: 150
Location: brisbane
|
|
|
|
yes, that will do fine ...
as I said, i could write it myself, but wondering if someone has done it in REXX.
(using this site online or as a possible web service is not an option ) |
|
| Back to top |
|
|
Pedro
Global Moderator
Joined: 01 Sep 2006
Posts: 2624
Location: Silicon Valley
|
|
|
|
| Quote: |
| but wondering if someone has done it in REXX. |
This sort of worked for me as an editor macro. It was part of another tool, so you might see a few things commented out.
| Code: |
/* rexx */
/*-------------------------------------------------------------------*/
/* Exec name . . : zzzESQLM */
/* Function . . : macro to split SQL statements */
/* */
/* Author . . . : Pedro Vera */
/* */
/*-------------------------------------------------------------------*/
/*-------------------------------------------------------------------*/
/* customization section */
/*-------------------------------------------------------------------*/
/* split line before */
/*trace('r')*/
srch.1 = ' AND ' ;indent.1 = 2
srch.2 = ' OR ' ;indent.2 = 3
srch.3 = ' ON ' ;indent.3 = 2
srch.4 = ' WHERE ' ;indent.4 = 0
srch.5 = ' LEFT ' ;indent.5 = 0
srch.6 = ' SELECT ' ;indent.6 = 0
srch.7 = ' GROUP ' ;indent.7 = 0
srch.8 = ' FROM ' ;indent.8 = 0
srch.9 = ' FOR ' ;indent.9 = 0
srch.10= ' WHEN ' ;indent.10= 3
srch.11= ' THEN ' ;indent.11= 6
srch.12= ' ELSE ' ;indent.12= 0
srch.13= ' END ' ;indent.13= 0
srch.14= 'MERGE ' ;indent.14= 2
srch.15= ' USING ' ;indent.15= 1
srch.16= ' UPDATE ' ;indent.16= 1
srch.17= ' INSERT ' ;indent.17= 1
srch.18= ' CASE ' ;indent.18= 1
srch.19= 'VALUES ' ;indent.19= 1
/* split line after */
after = 19 ;
srch.20= ';' ;indent.20= 0
srch.21= '+' ;indent.21= 0
srch.22= '-' ;indent.22= 0
srch.23= '/' ;indent.23= 0
srch.0 = 23
/* ignore within parens */
char.1 = 'VARCHAR(' ;
char.2 = ' CHAR(' ;
char.0 = 2
/*-------------------------------------------------------------------*/
/* 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)
/* remove quoted string To avoid false positives */
workline = Overlay(' ',workline, qt1+1, qt2-qt1-1)
qt = 0
End
qt0 = qt2 + 1
End
/*-------------------------------------------------------------------*/
/* 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. |
|
| Back to top |
|
|
jzhardy
Active User
Joined: 31 Oct 2006
Posts: 150
Location: brisbane
|
|
|
|
brilliant ... this is exactly the sort of thing I was looking for.
This will save me a lot of time!
many thanks |
|
| Back to top |
|
|
Pedro
Global Moderator
Joined: 01 Sep 2006
Posts: 2624
Location: Silicon Valley
|
|
|
|
| Quote: |
| brilliant ... this is exactly the sort of thing I was looking for. |
{Blushing} |
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
