What are the challenges you face when working across database platforms? Take the survey
Options

Strange formatting behaviour when parentheses are involved

Consider the following screenshots, where I have underlined what I consider to be formatting errors. The first screenshot includes my query as originally written.

In the first screenshot, I do not have the collapse option ticked. The formatter makes some very strange choices with respect to indenting. It doubly indents the nested parenthesized expression, but then unindents the "and" which is part of that expression, and also unindents the "or" which is part of the outer expression. This is obviously not the desired result.

In the second screenshot the nested indenting is no longer a problem because the expressions are collapsed, but note that it has now make a mistake regarding alignment. Specifically the alingment of the cross joined subquery is no longer aligned to the one above it.

Also, for some reason it has added two tabs after "select b" and before the "=" in the second subquery, but it did not do that for the first subquery, even though the structures are otherwise the same.

In both cases, the chosen settings on the "data (DML)" settings page are identical, as per the third screenshot.




Screenshot 1: No collapse







Screenshot 2: With collapse








Screenshot 3: DML settings


Tagged:

Answers

  • Options
    Tianjiao_LiTianjiao_Li Posts: 684 Rose Gold 5
    Would you be able to share your format style file?

    Thanks.
    Kind regards

    Tianjiao Li | Redgate Software
    Have you visited our Help Center?
  • Options
    allmhuranallmhuran Posts: 19 Bronze 2
    edited May 3, 2019 6:41AM
    Sure, attached.

    ( Edit:I  can't see my attempted attachement anywhere, so here it is on pastebin https://pastebin.com/P5Ds6A11 )

    I think some of the problems with trying to set up formatting probably come up because there are two different formatting "regimes" in a SQL statement.

    The first is the formatting for SQL clauses themselves.. select, from, where, and so on, the names of the relations being queried, joins, etc.

    But a second regime exists, which is a formatting regime for expressions. That might mean case statements, but it might also mean logical constructs used in where clauses, or inside the join, or function calls for column value expressions, and things like that.

    While SQL prompt does recognize case statements as an independently format-able construct in their own right, it doesn't seem to make that more general distinction between scalar-value-returning-expressions vs SQL clauses. For instance, the construct inside a where clause (or a join) is actually a scalar expression returning a boolean, and should fall under an "expression formatting regime" as opposed to a "clause formatting regime". You can think of the where clause as creating a sort of attachment point where an expression is about to go, and I should be able to set the "cursor start position" for that expression via SQL prompt, after which expression formatting should take over and act as if that chosen position was the left edge of the screen, so to speak.

  • Options
    Thanks. I've reproduced the issues and escalated to the development team. I'll keep you posted.
    Kind regards

    Tianjiao Li | Redgate Software
    Have you visited our Help Center?
  • Options
    Hi @allmhuran

    Thanks for your patience.

    Sadly, it's not something the development team will look into in the near future. They'll try to review formatting and the UI, but it won't be this year unfortunately.

    Our apologies for the inconvenience caused.
    Kind regards

    Tianjiao Li | Redgate Software
    Have you visited our Help Center?
Sign In or Register to comment.