Formatting question

ht1 · July 26, 2022, 1:34pm

Hello,
how can one achieve this:

SELECT
regexp_replace(
replace
(replace
(replace
(replace
(replace
(replace
(replace
(replace
(replace
(replace
(replace
(replace
(
replace ( replace ( REPLACE ( REPLACE ( REPLACE ( query, '`', '''' ), '$*tab', 'H' || UPPER ( 'USTVA' ) ),'$*tab', 'HTAB' ), chr (13), ''), chr (10), '') --v_stmt
, 'select', chr(10) || 'select ') --replace select with new line + select
, 'from ', chr(10) || 'from ') --replace from with new line + from
, 'where ', chr(10) || 'where ') --replace where with new line + where
, 'and ' , chr(10) || 'and ') --replace and with new line + and
, 'group by ', chr(10) || 'group by ') --replace group by with new line + group by
, 'set ' , chr(10) || 'set ') --replace set with new line + set
, 'case ', chr(10) || chr(9) || 'case ') --replace case with new line + tab + case
,'when ', chr(10) || chr(9) || chr(9) || 'when ') --replace when with new line + tab + tab + when
,'then ', chr(10) || chr(9) || chr(9) || chr(9) || 'then ') --replace then with new line + tab + tab + tab + then
,'else ', chr(10) || chr(9) || chr(9) || 'else ') --replace else with new line + tab + tab + else
, 'end ', chr(10) || chr(9) || 'end ') --replace end with new line + tab + end
, ',', ',' || chr(10)) --replace ',' with new line + ','
,'(','(' || chr(10),1,1) --replace first ( with ( + new line

ht1 · July 28, 2022, 1:07pm

I added a screenshot, this seems to show it better.

JohnDorlon · July 28, 2022, 1:09pm

What exactly are you trying to achieve? You just showed us an incomplete SQL.

Oh, I guess you are asking how to change the format options to get it to display as shown in your screenshot. @avergison is our formatting expert.

ht1 · July 28, 2022, 1:39pm

Hello John,
when I use toad with F7 to format the code, the code is somehow squezzing the "cascading Tree" of the replace statements.
I would like to achive that each replace keyword is on a seperate line and the corresponding replace statement also, indented with tabs. I will add, two more scrrenshots to clarify.
the example_code is what I would like to achive by using F7,
and after_f7 is what happend. the code samples are not the same.

this is not the same

ht1 · July 28, 2022, 1:40pm

example_code, not the same as the previous screenshot.

avergison · July 29, 2022, 7:45pm

Hello Horst,
I assume the option List Arrangements -> Parameters -> List Items -> List arrangement is set to "Stacked on Overflow or More than N", probably with N = 3.
Everything in your large statement is stacked due to margin overflow, so here's the result we would normally expect:

SELECT regexp_replace (
          replace (
             replace (
                replace (
                   replace (
                      replace (
                         replace (
                            replace (
                               replace (
                                  replace (
                                     replace (
                                        replace (
                                           replace (
                                              replace (
                                                 replace (
                                                    replace (
                                                       replace (
                                                          replace (query
                                                                  ,'`'
                                                                  ,'''')
                                                         ,'$*tab'
                                                         ,   'H'
                                                          || upper ('USTVA'))
                                                      ,'$*tab'
                                                      ,'HTAB')
                                                   ,chr (13)
                                                   ,'')
                                                ,chr (10)
                                                ,'')                  --v_stmt
                                             ,'select'
                                             ,chr (10) || 'select ') --replace select with new line + select
                                          ,'from '
                                          ,chr (10) || 'from ') --replace from with new line + from
                                       ,'where '
                                       ,chr (10) || 'where ') --replace where with new line + where
                                    ,'and '
                                    ,chr (10) || 'and ') --replace and with new line + and
                                 ,'group by '
                                 ,chr (10) || 'group by ') --replace group by with new line + group by
                              ,'set '
                              ,chr (10) || 'set ') --replace set with new line + set
                           ,'case '
                           ,chr (10) || chr (9) || 'case ') --replace case with new line + tab + case
                        ,'when '
                        ,chr (10) || chr (9) || chr (9) || 'when ') --replace when with new line + tab + tab + when
                     ,'then '
                     ,chr (10) || chr (9) || chr (9) || chr (9) || 'then ') --replace then with new line + tab + tab + tab + then
                  ,'else '
                  ,chr (10) || chr (9) || chr (9) || 'else ') --replace else with new line + tab + tab + else
               ,'end '
               ,chr (10) || chr (9) || 'end ') --replace end with new line + tab + end
            ,','
            ,',' || chr (10))                --replace ',' with new line + ','
         ,'('
         ,'(' || chr (10)
         ,1
         ,1)                               --replace first ( with ( + new line

Your main requests:
1/ The three inner REPLACE functions calls around v_stmt must be on a same line, while disregarding any margin overflow,
2/ All remaining outer REPLACEs have their 2nd and 3rd parameter on the same line.

The format you propose guarantees maximum code intelligibility indeed, I fully agree!
Unfortunately, stacking is all or nothing. If we do something special for REPLACE then what for other function calls?
Also, how can we know how many inner REPLACEs there are?

If there were some simple, mechanical criteria then perhaps something could be done. However, for now I don't immediately see a way to answer anything, sorry.

Andre

ht1 · August 3, 2022, 3:25pm

Hi Andre,

thanks for your answer. I played a little bit with the formatiting options.

None configuration can do what I would like to have, but for sure it is a complex theme. So I ‚m quite happy with the formatting, because i’m lazy when writing code.

Everything looks quite good, but these complex replace trees are a problem.

But not all the time one have to go into detail and find out, which replace don’t work as expected.

Again, thanks

mit freundlichen Grüßen / Best regards

Dipl.- Phys. Horst Teßmann
P +49 89 15984849 10

F +49 89 15984849 99

M ht@hot-itc.de

________HoT IT-Consulting GmbH
Am Burgsstall 8 D-82205 Gilching Germany

Geschäftsführer Horst Teßmann

Registergericht: Amtsgericht München

Registernummer: HRB 193722

Ust.-IdNr. gem. §27a UStG: DE 278665592

www.hot-itc.de

avergison · August 4, 2022, 12:59pm

Vielen Dank Horst for your reply. Perhaps slightly off-topic, but did you ever consider using REGEXP_REPLACE ? Here's an Oracle page with a few nice examples:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions130.htm

Andre

avergison · August 4, 2022, 1:01pm

I see you did higher up, but as far as I can see that may be a better solution than the numerous calls to REPLACE.

I meant something like this:

REGEXP_REPLACE(query, '(select|from |where |and |group by| .......)', chr(10) || '\1')

ht1 · August 4, 2022, 2:01pm

Hello Andre,

thanks for your reply, I just found regexp_replace as a „new“ function, I’m working on the package to simplify the replaces.

But in the beginning, it was a bit complicated to find the right syntax.

Stay healthy !