Code formatter SUBSTR expression problem

Toad for Oracle & DevOps Toolkit
1

Hello I’m using Toad 12.12.0.39 and I have a problem about formatting SUBSTR expressions. I’ve tried many options on Code Formatter but no success at all.

For example, I have a query

SELECT * FROM invoicejournal cij, unbilledorders unb WHERE SUBSTR (NLS_LOWER (cij.SALESID), 1, 43) = SUBSTR (NLS_LOWER (unb.ORDERNUM), 1, 43) AND SUBSTR (NLS_LOWER (cij.dataareaid), 1, 7) = SUBSTR (NLS_LOWER (unb.dataareaid), 1, 7) AND SUBSTR (NLS_LOWER (cij.dataareaid), 1, 7) = SUBSTR (NLS_LOWER (‘201’), 1, 7)

And when I format this code it looks like this:

/* Formatted on 16.04.2018 10:17:31 (QP5 v5.318) */
SELECT *
FROM invoicejournal cij, unbilledorders unb
WHERE SUBSTR (NLS_LOWER (cij.SALESID), 1, 43) =
SUBSTR (NLS_LOWER (unb.ORDERNUM), 1, 43)
AND SUBSTR (NLS_LOWER (cij.dataareaid), 1, 7) =
SUBSTR (NLS_LOWER (unb.dataareaid), 1, 7)
AND SUBSTR (NLS_LOWER (cij.dataareaid), 1, 7) =
SUBSTR (NLS_LOWER (‘201’), 1, 7)

But i want it like this:

SELECT *
FROM invoicejournal cij, unbilledorders unb
WHERE SUBSTR (NLS_LOWER (cij.SALESID), 1, 43) = SUBSTR (NLS_LOWER (unb.ORDERNUM), 1, 43)
AND SUBSTR (NLS_LOWER (cij.dataareaid), 1, 7) = SUBSTR (NLS_LOWER (unb.dataareaid), 1, 7)
AND SUBSTR (NLS_LOWER (cij.dataareaid), 1, 7) = SUBSTR (NLS_LOWER (‘201’), 1, 7)

What am i missing here? How can I edit code formatter to work like that?

Thank you!

2

Hello,

Perhaps the current right margin is too small to let each equation appear on a line by itself.

Using default options and when setting a margin of 87 or lower I’m getting

SELECT *
FROM invoicejournal cij, unbilledorders unb
WHERE SUBSTR (NLS_LOWER (cij.SALESID), 1, 43) =
SUBSTR (NLS_LOWER (unb.ORDERNUM), 1, 43)
AND SUBSTR (NLS_LOWER (cij.dataareaid), 1, 7) =
SUBSTR (NLS_LOWER (unb.dataareaid), 1, 7)
AND SUBSTR (NLS_LOWER (cij.dataareaid), 1, 7) =
SUBSTR (NLS_LOWER (‘201’), 1, 7)

As soon I my margin goes up to 97 I’m getting this:

SELECT *
FROM invoicejournal cij, unbilledorders unb
WHERE SUBSTR (NLS_LOWER (cij.SALESID), 1, 43) = SUBSTR (NLS_LOWER (unb.ORDERNUM), 1, 43)
AND SUBSTR (NLS_LOWER (cij.dataareaid), 1, 7) = SUBSTR (NLS_LOWER (unb.dataareaid), 1, 7)
AND SUBSTR (NLS_LOWER (cij.dataareaid), 1, 7) = SUBSTR (NLS_LOWER (‘201’), 1, 7)

As of margin 261 I’m even seeing the whole WHERE clause on a single line…

Or am I missing something?

Thanks,
Andre

3

Thanks Andre,

I’ve never thought it’s all might be about spacing/margin issue.

It worked like a charm!

Have good day,

Cenk