A few formatting requests

Discussion of open issues, suggestions and bugs regarding database management and development tools for SQL Server
Post Reply
AndrewBuis
Posts: 10
Joined: Tue 15 Nov 2011 16:13

A few formatting requests

Post by AndrewBuis » Thu 29 Mar 2012 15:23

Currently, there is no setting to seperate the AND/OR statements in a JOIN/ON statement to new lines.
e.g. Currently:

Code: Select all

	   FROM
		   dbo.CTF
		   INNER JOIN dbo.LIF
			   ON dbo.CTF.CONTRACT = dbo.LIF.CONTRACT
		   INNER JOIN dbo.POF
			   ON dbo.LIF.CONTRACT = dbo.POF.CONTRACT AND dbo.LIF.LINE = dbo.POF.LINE
		  LEFT OUTER JOIN dbo.CROSS_REFERENCE
			   ON dbo.POF.CONTRACT = dbo.CROSS_REFERENCE.CONTRACT AND dbo.POF.LINE = dbo.CROSS_REFERENCE.LINE AND dbo.POF.REL = dbo.CROSS_REFERENCE.REL
Future:

Code: Select all

		   dbo.CTF
		   INNER JOIN dbo.LIF
			   ON dbo.CTF.CONTRACT = dbo.LIF.CONTRACT
		   INNER JOIN dbo.POF
			   ON dbo.LIF.CONTRACT = dbo.POF.CONTRACT 
					AND dbo.LIF.LINE = dbo.POF.LINE
		  LEFT OUTER JOIN dbo.CROSS_REFERENCE
			   ON dbo.POF.CONTRACT = dbo.CROSS_REFERENCE.CONTRACT 
					AND dbo.POF.LINE = dbo.CROSS_REFERENCE.LINE 
					AND dbo.POF.REL = dbo.CROSS_REFERENCE.REL
Also, I would like to keep the AND/OR statements on the same line, and have an option to have the new line before the AND/OR statements.
e.g. Currently:

Code: Select all

WHERE
		   (dbo.POF.STATUS  dateadd(MONTH, -3, getdate())
Or

Code: Select all

WHERE
		   (dbo.POF.STATUS  dateadd(MONTH, -3, getdate())
Future:

Code: Select all

WHERE
		   (dbo.POF.STATUS  dateadd(MONTH, -3, getdate())
			OR POF.ITEMCLASS  '3A'

One last one: Option to have no non-tab whitespace before code for indenting.
e.g. Currently (t for tab, s for space)

Code: Select all

FROM
t     sss[Item]
t     sssINNER JOIN PurchaseOrder
t     t     sssON Item.Item = PurchaseOrder.Item
Future:

Code: Select all

FROM
t     [Item]
t     INNER JOIN PurchaseOrder
t     t     ON Item.Item = PurchaseOrder.Item

.jp
Devart Team
Posts: 345
Joined: Wed 09 Sep 2009 06:55
Location: devart

Post by .jp » Fri 30 Mar 2012 07:58

Could you please tell us what product (its version and edition) you are using?

AndrewBuis
Posts: 10
Joined: Tue 15 Nov 2011 16:13

Post by AndrewBuis » Fri 30 Mar 2012 15:17

dbForge SQL Complete
Standard Edition
Version 3.5.56

Microsoft SQL Server Management Studio 2008

alexa

Post by alexa » Mon 02 Apr 2012 12:11

> Currently, there is no setting to separate the AND/OR statements in a JOIN/ON statement to new lines.
> e.g. Currently:

This functionality will be implemented in one of the next builds of dbForge SQL Complete.

> Also, I would like to keep the AND/OR statements on the same line, and have an option to have the new line before the AND/OR statements.

To apply such kind of formatting, please follow the steps:

1. Select 'SQL Complete -> Options' from the main menu. The 'Options' window opens.
2. Select the 'Formatting -> New Lines' branch.
3. Select the 'Insert new line after"AND","OR" operators in logical expressions in "WHERE" and "HAVING" ' option.
4. Select the 'Place "AND","OR" operators in logical expressions in "WHERE" and "HAVING" on new line' option.

> One last one: Option to have no non-tab whitespace before code for indenting.
> e.g. Currently (t for tab, s for space)

To apply such kind of formatting, please follow the steps:

1. Select 'SQL Complete -> Options' from the main menu. The 'Options' window opens.
2. Navigate to the 'Formatting -> Indentation' branch.
3. Select the 'Indent clause content normally' option.

AndrewBuis
Posts: 10
Joined: Tue 15 Nov 2011 16:13

Re: A few formatting requests

Post by AndrewBuis » Fri 27 Apr 2012 14:08

alexa wrote:> Currently, there is no setting to separate the AND/OR statements in a JOIN/ON statement to new lines.
> e.g. Currently:

This functionality will be implemented in one of the next builds of dbForge SQL Complete.
Thanks!
alexa wrote: > Also, I would like to keep the AND/OR statements on the same line, and have an option to have the new line before the AND/OR statements.

To apply such kind of formatting, please follow the steps:

1. Select 'SQL Complete -> Options' from the main menu. The 'Options' window opens.
2. Select the 'Formatting -> New Lines' branch.
3. Select the 'Insert new line after"AND","OR" operators in logical expressions in "WHERE" and "HAVING" ' option.
4. Select the 'Place "AND","OR" operators in logical expressions in "WHERE" and "HAVING" on new line' option.
I want the new line BEFORE the AND/OR operators, not AFTER. Often times my clause may be longer then the screen, and I don't want to scroll out to see it. I also don't want line breaks messing up the flow.
alexa wrote: > One last one: Option to have no non-tab whitespace before code for indenting.
> e.g. Currently (t for tab, s for space)

To apply such kind of formatting, please follow the steps:

1. Select 'SQL Complete -> Options' from the main menu. The 'Options' window opens.
2. Navigate to the 'Formatting -> Indentation' branch.
3. Select the 'Indent clause content normally' option.
I have this option checked, I still get non-tab white space frequently.

alexa

Re: A few formatting requests

Post by alexa » Fri 27 Apr 2012 16:23

I want the new line BEFORE the AND/OR operators, not AFTER. Often times my clause may be longer then the screen, and I don't want to scroll out to see it. I also don't want line breaks messing up the flow.
Sorry, please do the following:

1. Select 'SQL Complete -> Options' from the main menu. The 'Options' window opens.
2. Select the 'Formatting -> New Lines' branch.
3. Unselect the 'Insert new line after"AND","OR" operators in logical expressions in "WHERE" and "HAVING" ' option.
4. Select the 'Place "AND","OR" operators in logical expressions in "WHERE" and "HAVING" on new line' option.
I have this option checked, I still get non-tab white space frequently.
We were not able to reproduce the problem.

We also noticed that you are using an old version of the product. Please upgrade dbForge SQL Complete to the latest version 3.5.60: http://www.devart.com/dbforge/sql/sqlco ... nload.html

In case the issue still persists, please provide us the SQL script where it happens along with the screenshot.

Since the information may be confidential, we suggest you to send it straight to our support system at supportATdevartDOTcom, so we will keep further correspondence with you via this system.

AndrewBuis
Posts: 10
Joined: Tue 15 Nov 2011 16:13

Re: A few formatting requests

Post by AndrewBuis » Fri 27 Apr 2012 16:34

Remove all the "3 spaces" and replace it with a "tab". I do have it on "Indent clause content normally"
Image
Also, I have updated, the screenshot is from awhile ago.

Code: Select all

ALTER FUNCTION timeDuration
(
    @startDate DATETIME,
    @endDate   DATETIME = NULL
)
RETURNS NVARCHAR(128)
AS
BEGIN
	IF @endDate IS NULL
		SET @endDate = getdate()

	RETURN CASE
			   WHEN datediff(SECOND, @startDate, @endDate) < 45 --3 tabs, 3 spaces
				   THEN --4 tabs, 3 spaces
					   'Just Now' --5 tabs, 3 spaces, etc
			   WHEN datediff(SECOND, @startDate, @endDate) < 90
				   THEN
					   '1 minute'
			   WHEN datediff(MINUTE, @startDate, @endDate) < 45
				   THEN
					   cast(datediff(MINUTE, @startDate, @endDate) AS NVARCHAR) + ' minutes'
			   WHEN datediff(MINUTE, @startDate, @endDate) < 75
				   THEN
					   '1 hour'
			   WHEN datediff(HOUR, @startDate, @endDate) < 9
				   THEN
					   CASE
						   WHEN datediff(MINUTE, @startDate, @endDate) - (datediff(HOUR, @startDate, @endDate) * 60) >= 15 AND datediff(MINUTE, @startDate, @endDate) - (datediff(HOUR, @startDate, @endDate) * 60) <= 45
							   THEN
								   cast(datediff(HOUR, @startDate, @endDate) AS NVARCHAR) + '.5 hours'
						   ELSE
							   cast(datediff(HOUR, @startDate, @endDate) AS NVARCHAR) + ' hours'
					   END
			   WHEN datediff(HOUR, @startDate, @endDate) < 22
				   THEN
					   cast(datediff(HOUR, @startDate, @endDate) AS NVARCHAR) + ' hours'
			   WHEN datediff(HOUR, @startDate, @endDate) < 36
				   THEN
					   '1 day'
			   WHEN datediff(DAY, @startDate, @endDate) <= 4
				   THEN
					   CASE
						   WHEN datediff(HOUR, @startDate, @endDate) - (datediff(DAY, @startDate, @endDate) * 24) >= 8 AND datediff(MINUTE, @startDate, @endDate) - (datediff(DAY, @startDate, @endDate) * 24) <= 16
							   THEN
								   cast(datediff(DAY, @startDate, @endDate) AS NVARCHAR) + '.5 days'
						   ELSE
							   cast(datediff(DAY, @startDate, @endDate) AS NVARCHAR) + ' days'
					   END
			   WHEN datediff(DAY, @startDate, @endDate) < 12
				   THEN
					   cast(datediff(DAY, @startDate, @endDate) AS NVARCHAR) + ' days'
			   WHEN datediff(WEEK, @startDate, @endDate) < 4
				   THEN
					   CASE
						   WHEN datediff(DAY, @startDate, @endDate) - (datediff(WEEK, @startDate, @endDate) * 7) >= 3 AND datediff(DAY, @startDate, @endDate) - (datediff(WEEK, @startDate, @endDate) * 7) <= 5
							   THEN
								   cast(datediff(WEEK, @startDate, @endDate) AS NVARCHAR) + '.5 weeks'
						   ELSE
							   cast(datediff(WEEK, @startDate, @endDate) AS NVARCHAR) + ' weeks'
					   END
			   WHEN datediff(DAY, @startDate, @endDate) < 38
				   THEN
					   '1 month'
			   WHEN datediff(MONTH, @startDate, @endDate) <= 6
				   THEN
					   CASE
						   WHEN datediff(DAY, @startDate, @endDate) - (datediff(MONTH, @startDate, @endDate) * 30) >= 10 AND datediff(DAY, @startDate, @endDate) - (datediff(MONTH, @startDate, @endDate) * 30) <= 20
							   THEN
								   cast(datediff(MONTH, @startDate, @endDate) AS NVARCHAR) + '.5 months'
						   ELSE
							   cast(datediff(MONTH, @startDate, @endDate) AS NVARCHAR) + ' months'
					   END
			   WHEN datediff(MONTH, @startDate, @endDate) < 12
				   THEN
					   cast(datediff(MONTH, @startDate, @endDate) AS NVARCHAR) + ' months'
			   WHEN datediff(MONTH, @startDate, @endDate) < 15
				   THEN
					   '1 year'
			   WHEN datediff(YEAR, @startDate, @endDate) <= 4
				   THEN
					   CASE
						   WHEN datediff(MONTH, @startDate, @endDate) - (datediff(YEAR, @startDate, @endDate) * 12) >= 4 AND datediff(MONTH, @startDate, @endDate) - (datediff(YEAR, @startDate, @endDate) * 12) <= 8
							   THEN
								   cast(datediff(MONTH, @startDate, @endDate) AS NVARCHAR) + '.5 years'
						   ELSE
							   cast(datediff(MONTH, @startDate, @endDate) AS NVARCHAR) + ' years'
					   END
			   ELSE
				   cast(datediff(YEAR, @startDate, @endDate) AS NVARCHAR) + ' years'
		   END
END

alexa

Re: A few formatting requests

Post by alexa » Sat 28 Apr 2012 06:50

We were able to reproduce the problem and will fix it in the next build of dbForge SQL Complete.

AndrewBuis
Posts: 10
Joined: Tue 15 Nov 2011 16:13

Re: A few formatting requests

Post by AndrewBuis » Sun 29 Apr 2012 14:26

Thanks

Post Reply