New training course: Customizing SQL Prompt. Watch now.

ON condition refactoring

philicophilico Posts: 1 New member
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.
Tagged:

Answers

  • 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.


Sign In or Register to comment.