A few formatting requests

[AndrewBuis]

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

[.jp]

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

[AndrewBuis]

dbForge SQL Complete
Standard Edition
Version 3.5.56

Microsoft SQL Server Management Studio 2008

[alexa]

> 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]

> 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!

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

> 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]

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]

Remove all the "3 spaces" and replace it with a "tab". I do have it on "Indent clause content normally"

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]

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

[AndrewBuis]

Thanks