ON condition refactoring

philico

Can we please have an option where the ON condition is on the same line as the JOIN clause?

As an example, the statement below (refactored by SQL-prompt):

    SELECT  c.AccountingPeriod

           ,p.EndMonthDate

           ,l.DeskID

           ,d.Desk

           ,COUNT(DISTINCT c.LoanID) 'LoanCount'

           ,COUNT(c.CashflowID) 'CashflowCount'

    FROM    dbo.tLOAN_CASHFLOW c

            JOIN tLoan l

            ON c.LoanID = l.LoanID

            JOIN tDesk d

            ON d.DeskId = l.DeskId

            JOIN tACCOUNTING_PERIOD p

            ON p.AccountingPeriod = c.AccountingPeriod

    WHERE   c.JournalPostingDate IS NULL

            AND c.loanid > 0

    GROUP BY c.accountingperiod

           ,p.EndMonthDate

           ,l.deskid

           ,d.Desk

Should be preferably refactored to:

    SELECT  c.AccountingPeriod

           ,p.EndMonthDate

           ,l.DeskID

           ,d.Desk

           ,COUNT(DISTINCT c.LoanID) 'LoanCount'

           ,COUNT(c.CashflowID) 'CashflowCount'

    FROM    dbo.tLOAN_CASHFLOW c

            JOIN tLoan l ON c.LoanID = l.LoanID

            JOIN tDesk d ON d.DeskId = l.DeskId             

            JOIN tACCOUNTING_PERIOD p ON p.AccountingPeriod = c.AccountingPeriod

    WHERE   c.JournalPostingDate IS NULL

            AND c.loanid > 0

    GROUP BY c.accountingperiod

           ,p.EndMonthDate

           ,l.deskid

           ,d.Desk

The latter SQL statement is more readable than the first. Also, it's easier to add additional joins in the latter statement without potentially breaking the syntax.

Grant

I'm looking at version 9 of SQL Prompt and that format choice is absolutely available. Just de-select "Place ON keyword on new line" and "Place ON condition on new line" and you'll get exactly what you want.