PL/SQL Beautifier issue with nested CASE statements

[Doug Hay]

I have noticed an issue when using nested case statements, the results from the beautifier are a little strange. See below:

SELECT NVL((CASE MAX(CASE SEC.ACCESS_PERMISSION_VALUE
WHEN 'N' THEN
0
WHEN 'R' THEN
1
WHEN 'U' THEN
2
WHEN 'F' THEN
3
ELSE
0
END) WHEN 0 THEN 'N' WHEN 1 THEN 'R' WHEN 2 THEN 'U' WHEN 3 THEN 'F' END), 'N')

The outer case statement seems to get ignored by the process. I have checked "THEN on new line" in the Control Structures options, so I am also surprised by that element of how the results appear. Is there something I can change to correct this behaviour or is this "just the way it is". I would like to see a result that looks something like the following:

SELECT NVL((CASE MAX(CASE SEC.ACCESS_PERMISSION_VALUE
WHEN 'N' THEN 0
WHEN 'R' THEN 1
WHEN 'U' THEN 2
WHEN 'F' THEN 3
ELSE 0
END)
WHEN 0 THEN 'N'
WHEN 1 THEN 'R'
WHEN 2 THEN 'U'
WHEN 3 THEN 'F'
END), 'N')

I checked how this looks using the "Preview Post" and the indent spacing gets lost, but I think the examples still gives the general idea.

 

[Marco Kalter]

This does indeed not look right, we'll try to fix it.

 

[a_kassabian]

PL/SQL Beautifier is one of the best new features of PL/SQL Developer 5.x. I hope you extend it soon to handle the new Oracle 9i constructs like MERGE.