Formating puts the ON clause in the wrong place

wsoranno

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

chriskelly

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.

wsoranno

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.

wsoranno

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.

chriskelly

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