Formating puts the ON clause in the wrong place

wsorannowsoranno Posts: 65
edited February 14, 2012 12:52PM in SQL Prompt Previous Versions
When using the WITH (NOLOCK) in a JOIN of the FROM clause, the ON clause is shifted out to the first parentheses of the WITH as in the following example:
FROM
    BillingProcess.dbo.Billing AS b WITH (NOLOCK)
    JOIN BillingProcess.dbo.v_Terms AS vt WITH (NOLOCK)
                                                                     ON b.YRTR = vt.Term

It should look like this:
FROM
    BillingProcess.dbo.Billing AS b WITH (NOLOCK)
    JOIN BillingProcess.dbo.v_Terms AS vt WITH (NOLOCK)
         ON b.YRTR = vt.Term


I am on version 5.2.2.1 of Sql Prompt.
Bill
Thanks
Bill Soranno
MCP, MCTS, MCITP DBA
Database Administrator
Winona State University
Maxwell 143
wsoranno@winona.edu

"Quality, like Success, is a Journey, not a Destination" - William Soranno '92

Comments

  • In SQL Prompts Options (SQL Prompt > Options) navigate to Format > Data Statements. In the section of this page titles "New lines" check that both the option "Join Condition" and "Align Join condition with JOIN" are both checked. My guess would be that the former is checked but the latter is not.
  • If I do not have the WITH statement, the format works properly.
    I will check the values when I get back into the office and let you know for sure.
    Thanks
    Bill Soranno
    MCP, MCTS, MCITP DBA
    Database Administrator
    Winona State University
    Maxwell 143
    wsoranno@winona.edu

    "Quality, like Success, is a Journey, not a Destination" - William Soranno '92
  • Chrisk5,
    Here are the values I have set for the New lines options:
    First Column = True
    Each subsequent column = True
    Contents of parentheses = True
    Closing parenthesis = True
    Join condition = True
    Indent subclasses = False
    Align join condition with JOIN = False

    If I set the Align join... to True here is what the statement will look like:
    FROM
        BillingProcess.dbo.Billing AS b WITH (NOLOCK)
        JOIN BillingProcess.dbo.v_Terms AS vt WITH (NOLOCK)
        ON b.YRTR = vt.Term
        JOIN BillingProcess.dbo.BillingType AS bt WITH (NOLOCK)
        ON b.BillingTypeId = bt.BillingTypeId
    

    If I set it to False and remove the WITH clauses this is what I want it to look like:
    FROM
        BillingProcess.dbo.Billing AS b
        JOIN BillingProcess.dbo.v_Terms AS vt
            ON b.YRTR = vt.Term
        JOIN BillingProcess.dbo.BillingType AS bt
            ON b.BillingTypeId = bt.BillingTypeId
    
    I am expecting the same behavior as the second example above when the WITH clause is present. I want the ON clause to be indented. It makes for easier readability.
    Thanks
    Bill Soranno
    MCP, MCTS, MCITP DBA
    Database Administrator
    Winona State University
    Maxwell 143
    wsoranno@winona.edu

    "Quality, like Success, is a Journey, not a Destination" - William Soranno '92
  • I have managed to confirm that SQL Prompt is aligning the condition to the previous keyword where the option "Align join condition with JOIN" is not selected. Unfortunately, there currently is not an option to change this behaviour.

    I will pass this along to the development team. But would it also be possible for you to post to the SQL Prompt UserVoice forum a suggestion to change this? This forum allows other users to comment and vote for features and the development team takes these into account.

    http://redgate.uservoice.com/forums/944 ... uggestions
Sign In or Register to comment.