When typing in something like:
SELECT LAST_VALUE (val ignore nulls) OVER (PARTITION BY set_id ORDER BY cdate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS last_val1 FROM DUAL
(all on one line in the Toad editor window)
...after clicking the Formatter icon, it does not format nicely. Instead it will format as:
SELECT LAST_VALUE (val ignore nulls) OVER (PARTITION BY set_id ORDER BY cdate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS last_val1
FROM DUAL
(on 3 lines, the first is "SELECT" and parameters, and the 2nd line is "AS last_val1", indented far right into the line, and 3rd "FROM DUAL" left aligned)
Below is what I'd call acceptable formatting for such long partitioning clause:
SELECT LAST_VALUE (val ignore nulls) OVER (PARTITION BY set_id
ORDER BY cdate
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) AS last_val1
FROM DUAL
(5 lines with the "BY" of 'partition by", 'order by", and the "BETWEEN" of 'rows between", and the "AND" of 'and current row' are lined up, indented in to the right. The SELECT and FROM are lined up along the left.)
This is an issue of newer Oracle syntax being introduced over the last couple of years, which is not covered by Formatter at this time.
WORKAROUND:
None
STATUS:
Enhancement request has been submitted to Development for consideration in future release of SQL Navigator for Oracle and Formatter Plus.
© 2021 Quest Software Inc. ALL RIGHTS RESERVED. Feedback Terms of Use Privacy