A few formatting requests

A few formatting requests

Postby 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 < '3')
         AND
         POF.DOCKDTE > dateadd(MONTH, -3, getdate())

Or
Code: Select all
WHERE
         (dbo.POF.STATUS < '3') AND
         POF.DOCKDTE > dateadd(MONTH, -3, getdate())


Future:
Code: Select all
WHERE
         (dbo.POF.STATUS < '3')
         AND POF.DOCKDTE > 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
AndrewBuis
 
Posts: 10
Joined: Tue 15 Nov 2011 16:13

Postby .jp » Fri 30 Mar 2012 07:58

Could you please tell us what product (its version and edition) you are using?
.jp
Devart Team
 
Posts: 232
Joined: Wed 09 Sep 2009 06:55
Location: devart

Postby AndrewBuis » Fri 30 Mar 2012 15:17

dbForge SQL Complete
Standard Edition
Version 3.5.56

Microsoft SQL Server Management Studio 2008
AndrewBuis
 
Posts: 10
Joined: Tue 15 Nov 2011 16:13

Postby 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.
alexa
Devart Team
 
Posts: 891
Joined: Fri 24 Jun 2011 14:17

Re: A few formatting requests

Postby 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.
AndrewBuis
 
Posts: 10
Joined: Tue 15 Nov 2011 16:13

Re: A few formatting requests

Postby 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.
alexa
Devart Team
 
Posts: 891
Joined: Fri 24 Jun 2011 14:17

Re: A few formatting requests

Postby 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
AndrewBuis
 
Posts: 10
Joined: Tue 15 Nov 2011 16:13

Re: A few formatting requests

Postby 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.
alexa
Devart Team
 
Posts: 891
Joined: Fri 24 Jun 2011 14:17

Re: A few formatting requests

Postby AndrewBuis » Sun 29 Apr 2012 14:26

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


Return to dbForge for SQL Server

cron