Toad World® Forums

Lack of indentation for AND - OR

Hi,

This is maybe an issue with the formatter, on Beta 6.0.0.299 at least. In

   Tools -> Options -> Editor -> Formatter -> Operators & Punctuations -> AND - OR

I select

   Style = "Stacked"
   Stacking Arrangment = 'Operators left, all conditions aligned when stacked, operators aligned with the DML keyword"

However, it seems that AND statements don’t get indented in WHERE clauses as specified. Please see the attached images for reference.

Apologies if I’m not being clear.

  • KJ

5751.Options.jpeg

Results.jpeg

I can simplify my question. When using the above Formatter choices for AND - OR, I can’t get the Formatter to turn this:

SELECT *
FROM t
WHERE @a <= a OR @b <= b;

into this:

SELECT
*
FROM
t
WHERE
@a <= a
OR @b <= b;

In fact, Formatter does nothing at all, as if it doesn’t know what to do.

The only setting that is causing problem is to set the options for AND-OR as attached, i.e. “Operators left, all conditions aligned when stacked, operators aligned with the DML keyword”. If I change that setting to any other option, Formatter “unblocks” itself and starts working again.

  • KJ
    Options.jpeg

I can simplify my question. When using the above Formatter choices for AND - OR, I can’t get the Formatter to turn this:

SELECT *
FROM t
WHERE @a <= a OR @b <= b;

into this:

SELECT
*
FROM
t
WHERE
@a <= a
OR @b <= b;

In fact, Formatter does nothing at all, as if it doesn’t know what to do.

The only setting that is causing problem is to set the options for AND-OR as attached, i.e. “Operators left, all conditions aligned when stacked, operators aligned with the DML keyword”. If I change that setting to any other option, Formatter “unblocks” itself and starts working again.

  • KJ
    Capture.jpeg

Hello KJ,

When I try to reproduce your case then it seems to work fine. Perhaps other options/settings did influence your results. Could you please copy me your actual options? Go to the top pane in the formatter options and press Copy Options to Clipboard.

Thanks,
Andre

Hi Andre,

Thank you very much for picking this up. Please see my options pasted below the horizontal line.

I’ve noticed that with AND - OR setting above, Formatter passes over entire sections of code, or simply behaves very strangely placing indents in unexpected places or not indenting at all. I’m just guessing out loud here, but it seems like WHERE isn’t treated the same as WHILE, etc. Would it be easier just to treat it the same as a DML keyword like SELECT? That’s how I would expect Formatter to behave with WHERE.

Best,
KJ


<FormatterOptions> <Version>252</Version> <LegacyOptionsGUI>5.252.0</LegacyOptionsGUI> <QP5>5.252</QP5> <InputTabs>3</InputTabs> <OutputTabs>2</OutputTabs> <IndentSize>2</IndentSize> <IndentTHENonSameLine>1</IndentTHENonSameLine> <IndentLOOPonSameLine>1</IndentLOOPonSameLine> <IndentAfterProcs>htp.blockquoteOpen\nhtp.bodyOpen\nhtp.centerOpen\nhtp.dirlistOpen\nhtp.dlistOpen\nhtp.fontOpen\nhtp.formOpen\nhtp.formSelectOpen\nhtp.formTextareaOpen\nhtp.framesetOpen\nhtp.headOpen\nhtp.htmlOpen\nhtp.listingOpen\nhtp.mapOpen\nhtp.menulistOpen\nhtp.noframesOpen\nhtp.preOpen\nhtp.tableOpen\nhtp.tableRowOpen\nhtp.ulistOpen\n</IndentAfterProcs> <RightMargin>999</RightMargin> <LF_ConsecLFLimit>1</LF_ConsecLFLimit> <VarDeclAlignment>0</VarDeclAlignment> <VarDeclDynMinSpace>1</VarDeclDynMinSpace> <ParamDeclDynMinSpace>1</ParamDeclDynMinSpace> <PassedParamDynMinSpace>1</PassedParamDynMinSpace> <PassedParamOperatorLeft>1</PassedParamOperatorLeft> <AssignAlignmentFixedOffset>1</AssignAlignmentFixedOffset> <AssignAlignmentDynMinSpace>1</AssignAlignmentDynMinSpace> <AssignFavorWrapping>0</AssignFavorWrapping> <AndOrStyle>1</AndOrStyle> <AndOrOperatorPos>2</AndOrOperatorPos> <AddMulStyle>0</AddMulStyle> <AddMulOperatorPos>2</AddMulOperatorPos> <CaseAllowFlat>0</CaseAllowFlat> <CommentsRightJustifyTrailing>0</CommentsRightJustifyTrailing> <DefaultArrangement>S</DefaultArrangement> <DefaultArrangeOnCol>1</DefaultArrangeOnCol> <ParamDeclArrangement>S</ParamDeclArrangement> <ParamDeclArrangeOnCol>1</ParamDeclArrangeOnCol> <PassedParamArrangement>W</PassedParamArrangement> <PassedParamArrangeOnCol>1</PassedParamArrangeOnCol> <SelectListArrangement>S</SelectListArrangement> <SelectListArrangeOnCol>1</SelectListArrangeOnCol> <ColumnArrangement>W</ColumnArrangement> <ColumnArrangeOnCol>1</ColumnArrangeOnCol> <AttributeArrangement>W</AttributeArrangement> <ExprArrangement>W</ExprArrangement> <ExprArrangeOnCol>1</ExprArrangeOnCol> <DmlKeywordsLeftAligned>3</DmlKeywordsLeftAligned> <DmlMiddleMargin>2</DmlMiddleMargin> <DmlRhsAlignment>0</DmlRhsAlignment> <Tagline>0</Tagline> </FormatterOptions>

Hi KJ,

The at-signs in the name in combination with specific AND-OR and DML statement option values caused the formatter not to format. This has been fixed. You’ll see the change shortly whenever you get to download a beta having a QP5.dll file with version 5.253 or higher.

Thanks for reporting,

Andre

Hi Andre,

Much appreciated, thanks!

Stupid question #1: Would my colleagues and I be able to use a beta QP5.dll file in a production 6.0 release?

Stupid question #2: Out of curiosity, is there any overlap with the parsers for the Formatter and the parser for execute-current-statement? See this thread starting at the end of page 2:

www.toadworld.com/…/12784.aspx

  • KJ

Hi KJ,

Who said that there is no such thing as a stupid question? [:)]

#1: You can try to do so, QP5 is normally backwards compatible but no guarantee! There is no official support as the combination has never been tested. So in case you need Dell support you’ll have to restore the original QP5.dll file first.

#2: The formatter uses the syntax parser, so any syntax issues will also affect formatting.

Hopes this helps,

Andre

Hi Andre,

Well, I felt stupid while typing the questions!

Thank you very much. I’ll just try it and see what happens, whenever a new version of QP5.dll comes out. I’ll probably be running the beta because I’m always the guinea pig, but my team is in the midst of deciding what version of Toad to purchase and they would of course be keenly interested in being able to use these small parser fixes without switching to beta.

It was just a thought. Probably stupid …

Best,

KJ

Hi Andre,

Very close: I just downloaded Beta 6.1 and can see that the Formatter doesn’t choke anymore when encountering an @ sign. However, it is still not indenting properly: AND and OR are always at the level of the WHERE statement, rather than with the first line after the WHERE, like this:

4747.Capture.jpeg

Best,

KJ

I’ll optimize this asap.

Incidentally, I discovered that the same problem exists in the ON statement for MERGE INTO. For example:

Note the similarly unindented "AND t1.Here = t2.There" in the final line.

Best,

KJ

Thanks for mentioning this, most useful.

Andre

Hi,

It looks like the @ symbol combined with an = assignment in the SELECT statement also causes the parser to blink:

4137.Capture.jpeg

This is true even with the QP5.dll from the latest Toad for Oracle beta, i.e. version 5.254.

Thanks,

KJ

KJ,

Using qp5 dll version 5.253 and setting a margin of 30 I’m getting this:

SELECT @var = 1

FROM Table1 t1
     INNER JOIN Table2 AS t
     ON t1.Col = t2.Col;

There was a problem with formatting variables in 5.252 and older.

Do you get the same result?

Ah, yes I get a similar result with 5.253. Setting the margin to 43 gives the right formatting; setting it to 52 causes the parser trouble (OK at 51). Here is my screen, again using the “Defaults” Formatter with just the change to “Right Margin”:

6685.Capture.jpeg

We always operate with Right Margin set to 999 because we do not want to wrap (e.g. the INSERT INTO line is never wrapped). So I guess it’s making all the parsing errors come out all at once [:O]

Best,

KJ