Treating AND and OR in JOIN clause differently than in WHERE

anacap

I currently like to format my AND and OR keywords on a new line indented in my WHERE clause but want them on the same line when I'm joining two tables on multiple columns.  The current behavior treats them no differently and if I join two tables on more than one column, the second (or more) columns are put on their own lines.  If there's no current way to do this, is there a possibility this will be addressed in a future version?

Dan_J

Hi @anacap

Thanks for raising this with us.

Just to avoid any ambiguity at all around this (I generally always ask this), would you mind providing a basic example script showing the formatting you would ideally like to be able to achieve?

anacap

It would look like the following:

SELECT  a.Col1,
                b.Col2
FROM     dbo.Table1 a
                INNER JOIN dbo.Table2 b ON a.Col3 = b.Col3 AND a.Col4 = b.Col4
WHERE   a.Col5 = 3
                AND b.Col6 = 5;


Currently there is no distinction for the AND in the JOIN clause and the current behavior is to place the first AND on the next line (the way it is in the WHERE clause).  

It would be nice to have a distinction there.  I personally like having my joined tables and how they are joined all on one line so I can quickly see how many joined tables/views are involved.

Dan_J

Hi @anacap

Thanks for providing an example for this.

Please correct me if I am wrong or misunderstanding anything here, but does this look how you would like it to?:

anacap

No, I think you're misunderstanding my request.  The sample generated does not reflect what I mentioned because the two predicates in the WHERE clause are still on 1 line.

I want the FROM clause to look the way it does in your sample code.  That part looks how I would like it.  However, the WHERE clause is all on one line which is not what I like it to be.

It seems the Operators section in the style formatting should appear twice: Under Clauses-JOIN and create another: Clauses-WHERE and then offer AND/OR options.  Then what I would do is for Clauses-JOIN is set Place On New Line to NEVER and in Clauses-WHERE set Place On New Line to Always with Alignment set to To First List Item.

Or perhaps just have one setting under Clauses-JOIN and a checkbox to that says, "Keep all JOIHN conditions on the same line as Table/View"

Dan_J

Hi @anacap

Thanks for clarifying on this, my apology for the delay in coming back to you.

To confirm, it is definitely not currently possible to achieve this formatting with SQL Prompt (because of no AND/OR options being available for WHERE clauses).

As this is a feature request, please can you log this on our User Voice page for our development teams visibility (https://redgate.uservoice.com/forums/94413-sql-prompt)?