Devart Forums

Posted: **Tue 14 Apr 2020 22:22**

I am struggling a little bit in setting up my custom SQL Complete formatting profile.

I have not been able to accomplish the following format options:

Indent JOINs in a hierarchical fashion, starting 1 space aligned with the FROM Clause, and then cascading 1 space after that for every JOIN that relies on the parent above. I the hierarchy is is impossible i would settle for simply aligned with FROM and indented 1 space, but i cannot even make that work.

Align the JOIN's ON conditions with the condition in the first ON command such that all the conditions content is aligned.

Align the WHERE conditions such that all of the content is aligned. With special focus on nested conditions aligning with internal content.

I have not even attempted the Subquery but i'm am hopeful once I figure out the primary formatting functions the rest wont be too difficult assuming we reposition the start point of all the subquery elements (except the SELECT clause) to position from 2 characters after the JOIN keyword.

This is the Desired Result:

Code: Select all

SELECT C.Customer_Number
      ,ST.Business_Name
      ,SY.Alarm_Account
      ,TC.Ticket_Count
FROM AR_Customer C
 JOIN AR_Customer_Site ST ON C.Customer_Id = ST.Customer_Id
  JOIN AR_Customer_System SY ON ST.Customer_Site_Id = SY.Customer_Site_Id
                            AND ST.Customer_Id = SY.Customer_Id
   CROSS APPLY (SELECT COUNT(*) AS 'Ticket_Count'
                FROM SV_Service_Ticket T
                WHERE T.Customer_System_Id = SY.Customer_System_Id
                 AND T.Customer_Site_Id = SY.Customer_Site_Id
                 AND T.Customer_Id = SY.Customer_Id) TC
WHERE SY.Inactive <> 'Y'
 AND (C.Customer_Number BETWEEN 10 AND 100)
 AND (C.Customer_Type_Id = 10
   OR C.Customer_Status_Id = 5)

Instead this is what i'm getting:

Code: Select all

SELECT C.Customer_Number
      ,ST.Business_Name
      ,SY.Alarm_Account
      ,TC.Ticket_Count
FROM AR_Customer C
JOIN AR_Customer_Site ST ON C.Customer_Id = ST.Customer_Id
JOIN AR_Customer_System SY ON ST.Customer_Site_Id = SY.Customer_Site_Id
AND ST.Customer_Id = SY.Customer_Id
CROSS APPLY (
SELECT COUNT(*) AS 'Ticket_Count'
FROM SV_Service_Ticket T
WHERE T.Customer_System_Id = SY.Customer_System_Id
 AND T.Customer_Site_Id = SY.Customer_Site_Id
 AND T.Customer_Id = SY.Customer_Id
) TC
WHERE SY.Inactive <> 'Y'
 AND (C.Customer_Number BETWEEN 10 AND 100)
 AND (C.Customer_Type_Id = 10 OR C.Customer_Status_Id = 5)

Thanks for any suggestions.

Brad Swindell

Posted: **Thu 16 Apr 2020 12:19**

You can try the following settings

Posted: **Thu 16 Apr 2020 13:00**

Thank you for the reply.

Could you export the settings file for me?

I have tied to match these settings and the JOINs would not indent at all.
Also the you have the ON conditions intended a fixed 24 columns. That is not going to work as the length of the table names are not fixed and the indents on the joins will move the ON command around and thus the 24 columns would not work.

This is the output those settings generated:

Code: Select all

SELECT C.Customer_Number
			,ST.Business_Name
			,SY.Alarm_Account
			,TC.Ticket_Count
FROM AR_Customer C
JOIN AR_Customer_Site ST ON C.Customer_Id = ST.Customer_Id
JOIN AR_Customer_System_Notes SY ON ST.Customer_Site_Id = SY.Customer_Site_Id
													AND ST.Customer_Id = SY.Customer_Id
CROSS APPLY (
SELECT COUNT(*) AS 'Ticket_Count'
FROM SV_Service_Ticket T
WHERE T.Customer_System_Id = SY.Customer_System_Id
	AND T.Customer_Site_Id = SY.Customer_Site_Id
	AND T.Customer_Id = SY.Customer_Id
) TC
WHERE SY.Inactive <> 'Y'
	AND (C.Customer_Number BETWEEN 10 AND 100)
	AND (C.Customer_Type_Id = 10 OR C.Customer_Status_Id = 5)

Posted: **Thu 16 Apr 2020 16:24**

We have sent it to you.

Devart Forums

SQL Complete Formatting Alignment Issues

SQL Complete Formatting Alignment Issues

Re: SQL Complete Formatting Alignment Issues

Re: SQL Complete Formatting Alignment Issues

Re: SQL Complete Formatting Alignment Issues