Red Gate forums :: View topic - Formatting requests - post them here
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Refactor 1
SQL Refactor 1 forum

Formatting requests - post them here

Search in SQL Refactor 1 forum
Post new topic   Reply to topic
Jump to:  
Go to page Previous  1, 2, 3, 4
Author Message
Lxocram



Joined: 07 Jun 2007
Posts: 16
Location: Belgium, Ghent

PostPosted: Fri Oct 10, 2008 3:18 pm    Post subject: Reply with quote

remove empty lines option in layout sql
Back to top
View user's profile Send private message MSN Messenger
willspurgeon



Joined: 16 Jan 2009
Posts: 5

PostPosted: Fri Jan 16, 2009 10:12 pm    Post subject: Re: Feature request Reply with quote

I second this comment.

doclane wrote:
Indent line comments with next or previous line
This:
Code:

BEGIN
       SET NOCOUNT ON
--set mostrecentstat flag to no in historical table
       UPDATE
               dbo.tbHistoricalDBFileInfo
       SET
               mostrecentstat = 0
       WHERE
--move current to historical table with mostrecentstat flag set to yes
                mostrecentstat = 1

To This:
Code:

BEGIN
       SET NOCOUNT ON
       --set mostrecentstat flag to no in historical table
       UPDATE
             dbo.tbHistoricalDBFileInfo
       SET
             mostrecentstat = 0
       WHERE
              --move current to historical table with mostrecentstat flag set to yes
              mostrecentstat = 1
Back to top
View user's profile Send private message
willspurgeon



Joined: 16 Jan 2009
Posts: 5

PostPosted: Fri Jan 16, 2009 10:26 pm    Post subject: Re: JOIN layout and tabs vs spaces Reply with quote

I RADICALLY second this comment. To put the joined table on the new line is my #1 feature request for SQL Layout. My final style ends up as:

Code:

SELECT
    F.FieldA,
    S.FieldB,
    T.FieldC
FROM
    FirstTable F
INNER JOIN
    SecondTable S
        ON F.Key1 = S.Key1
INNER JOIN
    ThirdTable T
        ON S.Key2 = T.Key2


Having the join table on a new line and indented, and the join condition on a new line, indented again, makes it much easier for me to quickly scan the join types vs. the join tables vs. the join conditions. I would love this feature.

Thanks!


Viking Geek wrote:
Rather than:

Code:
SELECT
    *
FROM
    HumanResources.Employee AS e
INNER JOIN Person.Contact AS c ON e.ContactID = c.ContactID
WHERE
   c.ContactID = 25
ORDER BY
    c.LastName


I would prefer to have the joined table and condition on a new line, like this:

Code:
SELECT
    *
FROM
    HumanResources.Employee AS e
INNER JOIN
    Person.Contact AS c ON e.ContactID = c.ContactID
WHERE
   c.ContactID = 25
ORDER BY
    c.LastName


I think that is more consistent with the layout, as major keywords are alone on each line.

I would also prefer to use tabs over spaces for indents.
Back to top
View user's profile Send private message
howarthcd



Joined: 16 May 2007
Posts: 50

PostPosted: Mon Jan 19, 2009 9:18 am    Post subject: Reply with quote

Rather than having so many options, as you'll never please everyone, how about allowing SQL Refactor to provide a piece of sample code that the user modifies to suit (perhaps through the options dialog), and then SQL Refactor could base future auto-layouts on an analysis of the modifications made to the sample code.

Chris
Back to top
View user's profile Send private message
CarlUman



Joined: 22 Jan 2009
Posts: 1

PostPosted: Thu Jan 22, 2009 8:51 pm    Post subject: Reply with quote

#1 for me has already been asked for a number of times...

"lowercase keywords? nobody need to shout at their database Smile "
Back to top
View user's profile Send private message
peterzeke



Joined: 10 Feb 2009
Posts: 1

PostPosted: Tue Feb 10, 2009 4:48 pm    Post subject: Option to have blank line before each Join Reply with quote

Hello:

Generally speaking I find SQL Refactor to be an awesome product -- especially when I need to refactor a coworker's script so that I can read it. I do have a feature request, however.

As a data and reporting analyst, I end up scripting complicated SQL that typically relies on joining multiple tables/views/udf tables together wherein the keys that join these objects can involve multiple keys, too.

Rather than having SQL Refactor stack the various joins on top of one another, I prefer creating a line space between each set of Join statements for easier reading. Eassentially the preferred layout is to have the JOIN keywords on one line, the object being joined on a second line, and then the Join criteria ("on...") on a third line.

For example:
Code:


SELECT
     A.PatientName
    ,B.Provider
    ,C.LabDesc
    ,D.ResultDate
    ,D.Result

FROM
    dbo.Dim_Patients A

    LEFT JOIN
    dbo.Dim_Provider B
    on A.PatientKey = B.PatientKey
    and A.LocationKey = B.LocationKey

    LEFT JOIN
    dbo.Dim_Lab C
    on A.PatientKey = C.PatientKey
    and A.InsuranceKey = C.InsuranceKey 

    LEFT JOIN
    dbo.Fact_MeasureResults D
    on A.PatientKey = D.PatientKey
    and C.LabKey = D.LabKey





Although the above example code is actually quite brief and uncomplicated, it is nevertheless exceptionally easy to read. Consequently, I would be very appreciative if SQL Refactor could include the option to create line breaks between Join clauses, as well as the option to place joined objects and the join criteria on separate lines.

Cheers!
Back to top
View user's profile Send private message
Paul Richmond



Joined: 13 May 2009
Posts: 2
Location: Tacoma, Washington

PostPosted: Wed May 13, 2009 10:52 pm    Post subject: Formatting of Case statments. Reply with quote

It would be nice to have some more options for formatting case.....

Currently Produces.....
Code:
                           ,CASE L.[ACCT] /* Massage the account type into predetermined categories */
                              WHEN 500 THEN 'Sales'
                              WHEN 600 THEN 'COS'
                              ELSE CASE WHEN L.Sub < 75700000
                                        THEN 'Fixed Costs'
                                        WHEN L.Sub < 79099999
                                        THEN 'Variable Costs'
                                        WHEN L.Sub < 80020000
                                        THEN 'Contra Allocation'
                                        ELSE 'Units'
                                   END
                            END AS [Group]


Would like.....
Code:
                           ,CASE L.[ACCT] /* Massage the account type into predetermined categories */
                              WHEN 500 THEN 'Sales'
                              WHEN 600 THEN 'COS'
                              ELSE CASE WHEN L.Sub < 75700000 THEN 'Fixed Costs'
                                        WHEN L.Sub < 79099999 THEN 'Variable Costs'
                                        WHEN L.Sub < 80020000 THEN 'Contra Allocation'
                                        ELSE 'Units'
                                   END
                            END AS [Group]


It appears that there are several instances where 'nested' formatting is not consistant.
_________________
Paul Richmond
Back to top
View user's profile Send private message Send e-mail
Paul Richmond



Joined: 13 May 2009
Posts: 2
Location: Tacoma, Washington

PostPosted: Wed May 13, 2009 10:57 pm    Post subject: Long lines not 'wrapping' Reply with quote

I am having specific instances where long lines don't appear to wrap. Example below:

Code:
                FROM    (SELECT [Description]
                               ,[Group]
                               ,[sub]
                               ,[type]
                               ,Per
                               ,AMOUNT
                         FROM   [AccountData_CTE]
                         /* The period, (1 - 24 due to budget values) is used to determine which column the amount drops into*/
                        ) P PIVOT ( SUM(P.Amount) FOR Per IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24]) ) AS Pvt
                ORDER BY [Pvt].[Group]
                       ,[Pvt].[Description]               


Wonderful product by the way.... I LOVE your configuration dialogs.
_________________
Paul Richmond
Back to top
View user's profile Send private message Send e-mail
Michael.Kriegner



Joined: 29 Aug 2006
Posts: 7

PostPosted: Wed Jun 17, 2009 11:16 am    Post subject: Feature request Reply with quote

First of all, thank you for the great layout functions and I really hope you can realize most/all of the requested features of all users, because all of them are very suitable for different tasks/projects.

Most/many features I need, are already implemented. But following formatting I would need for our company guidelines (especially our very special indentation for JOINs and CASEs):

Code:

DECLARE @x AS NVARCHAR( MAX ) -- watch the spaces (even with "pad the inside of parantheses with spaces" on; comment should stay in this line; remove redundant AS
DECLARE @i AS INTEGER -- replace INTEGER to INT

INSERT INTO TableX -- remove redundant INTO (if exists), name should be in the same line
(
   Column1,
   Column2,
   Column3
)
SELECT
   Table2.a,
   Table2.b,
   Table3.c
FROM Table2
 INNER JOIN Table3 -- INNER JOIN just one space after FROM
  ON Table2.ID = Table3.ID -- ON just one space after INNER JOIN
WHERE Table2.x = 1

INSERT TableX
(
   Column1,
   Column2,
   Column3
)
VALUES
(
   1,
   2,
   @x
)

DELETE FROM Database1.dbo.TableY -- remove redundant FROM (if exists), name should be in the same line
FROM Database1.dbo.TableY TableY
 INNER JOIN TableZ
  ON TableY.ID = TableZ.ID
WHERE TableZ.x = @x

SELECT ColumnA -- only one column should stay in the same line
FROM TableA
WHERE ID = 1

SELECT
   CASE
   WHEN ColumnA = 1
   THEN 'a'
   WHEN ColumnB = 2
   THEN 'b'
   ELSE
      CASE
      WHEN ColumnC = 3
      THEN 'c'
      ELSE '?'
      END
   END AS TestCase
FROM TableA

UPDATE TableA
SET ColumnA = @x

UPDATE TableA
SET
   ColumnA = @x,
   ColumnB = 1


After layouting, I get this:

Code:

DECLARE @x NVARCHAR(MAX)
 -- watch the spaces (even with "pad the inside of parantheses with spaces" on; comment should stay in this line

INSERT
    TableX -- remove redundant INTO, name should be in the same line
    (
      Column1,
      Column2,
      Column3
    )
    SELECT
        Table2.a,
        Table2.b,
        Table3.c
    FROM
        Table2
    INNER JOIN Table3
    -- INNER JOIN just one space after FROM
        ON
        Table2.ID = Table3.ID -- ON just one space after INNER JOIN
    WHERE
        Table2.x = 1

INSERT
    TableX
    (
      Column1,
      Column2,
      Column3
    )
VALUES
    ( 1, 2, @x )

DELETE
    Database1.dbo.TableY -- remove redundant FROM, name should be in the same line
FROM
    Database1.dbo.TableY TableY
INNER JOIN TableZ
ON  TableY.ID = TableZ.ID
WHERE
    TableZ.x = @x

SELECT
    ColumnA -- only one column should stay in the same line
FROM
    TableA
WHERE
    ID = 1

SELECT
    CASE WHEN ColumnA = 1 THEN 'a'
         WHEN ColumnB = 2 THEN 'b'
         ELSE CASE WHEN ColumnC = 3 THEN 'c'
                   ELSE '?'
              END
    END AS TestCase
FROM
    TableA

UPDATE
    TableA
SET ColumnA = @x

UPDATE
    TableA
SET ColumnA = @x, ColumnB = 1


For the layout functions we also could need a developer edition with APIs including Syntax Highlighting (like you made for SQL Compare and SQL Data Compare) for our developers to integrate in our own development tools (for internal use).

Thank you very much in advance,

Michael Kriegner
Back to top
View user's profile Send private message
MartinB



Joined: 04 Sep 2009
Posts: 5

PostPosted: Fri Sep 04, 2009 10:51 am    Post subject: case statements Reply with quote

Hi it would be nice if Refactor layed out complex case statements sensibly
I would like each case on a new line and all indented the same as in the first example

e.g

Code:

SELECT  *
FROM    (
         SELECT    ROW_NUMBER() OVER ( ORDER BY
         CASE WHEN @OrderByColumn = 'Amount'
         AND @SortDirection = 'ASC' THEN Batch.Amount
         END ASC,
         CASE WHEN @OrderByColumn = 'Amount'
         AND @SortDirection = 'DESC' THEN Batch.Amount
         END DESC,
         CASE WHEN @OrderByColumn = 'ClubName'
         AND @SortDirection = 'ASC' THEN ClubName
         END ASC,
         CASE WHEN @OrderByColumn = 'ClubName'
         AND @SortDirection = 'DESC' THEN ClubName
         END DESC,
         CASE WHEN @OrderByColumn = 'BillingReference'
         AND @SortDirection = 'ASC' THEN Batch.BillingReference
         END ASC,
         CASE WHEN @OrderByColumn = 'BillingReference'
         AND @SortDirection = 'DESC' THEN Batch.BillingReference
         END DESC ) AS RowNumber,*
          FROM      Batch
        ) AS PageRequests
WHERE   PageRequests.RowNumber BETWEEN ( @pageIndex )
                  AND     ( @pageIndex + @pageSize )


Becomes

Code:

        SELECT  *
        FROM    (
                  SELECT    ROW_NUMBER() OVER ( ORDER BY CASE WHEN @OrderByColumn = 'Amount'
                                                                   AND @SortDirection = 'ASC' THEN Batch.Amount
                                                         END ASC, CASE WHEN @OrderByColumn = 'Amount'
                                                                            AND @SortDirection = 'DESC' THEN Batch.Amount
                                                                  END DESC, CASE WHEN @OrderByColumn = 'ClubName'
                                                                                      AND @SortDirection = 'ASC' THEN ClubName
                                                                            END ASC, CASE WHEN @OrderByColumn = 'ClubName'
                                                                                               AND @SortDirection = 'DESC' THEN ClubName
                                                                                     END DESC, CASE WHEN @OrderByColumn = 'BillingReference'
                                                                                                         AND @SortDirection = 'ASC' THEN Batch.BillingReference
                                                                                               END ASC, CASE WHEN @OrderByColumn = 'BillingReference'
                                                                                                                  AND @SortDirection = 'DESC' THEN Batch.BillingReference
                                                                                                        END DESC ) AS RowNumber,
                            *
                  FROM      Batch
                ) AS PageRequests
        WHERE   PageRequests.RowNumber BETWEEN ( @pageIndex )
                                       AND     ( @pageIndex + @pageSize )
Back to top
View user's profile Send private message
Anu Deshpande



Joined: 20 Apr 2009
Posts: 692
Location: Cambridge

PostPosted: Fri Sep 04, 2009 4:14 pm    Post subject: Reply with quote

Thanks for your post.

I have logged this issue as a feature request (SR-690) so that it can be reviewed by our development team and incorporate in our future release.

Kindly let us know if you have any issues with SQL Refactor, I'll like to help.
_________________
Anuradha Deshpande
Product Support
Redgate Software Ltd.
E-mail: support@red-gate.com
Back to top
View user's profile Send private message Send e-mail
MartinB



Joined: 04 Sep 2009
Posts: 5

PostPosted: Tue Sep 08, 2009 12:55 pm    Post subject: Reply with quote

Hi

Another layout suggestion.
It would be nice if calls to stored procs used the same comma usage as insert statements etc e.g.
Code:

EXEC dbo.usp_CalledsProc @Param1, @Param2, @Param3, Param4, @Param5, @Param6,
    @Param7, @Param8, @Param9, @Param10





would become

Code:

EXEC dbo.usp_CalledsProc
@Param1,
                   @Param2,
                   @Param3,
                   @Param4,
                   @Param5,
                   @Param6,
                   @Param7,
                   @Param8,
                   @Param9,
                   @Param10

Back to top
View user's profile Send private message
dbbishop



Joined: 13 Feb 2009
Posts: 11
Location: Kansas City, MO

PostPosted: Thu Sep 10, 2009 1:20 am    Post subject: Another Layout Suggestion Reply with quote

I don't know if this has been brought up, but I would like to see Refractor preserve the case of object name (columns, procs, tables) when laying out existing SQL code.

Fof example, if I have a table named Products with the columns ProductID, ProductName, CostPerUnit, Unit as defined in the DML, and I have the following code:

SELECT productid, productname FROM products

After laying out the code, it would be changed to:

Code:
SELECT ProductID,
            ProductName
FROM    Products
Back to top
View user's profile Send private message
Lxocram



Joined: 07 Jun 2007
Posts: 16
Location: Belgium, Ghent

PostPosted: Wed Mar 24, 2010 1:00 pm    Post subject: CONDITIONAL code and BOOLEAN between parentheses Reply with quote

ELSE IF as one operator
instead of:
Code:

IF
...
ELSE IF
        ...
        ELSE IF
                ...
                ELSE IF

statement should look like
Code:

IF
...
ELSE IF
...
ELSE IF

...
ELSE


BOOLEAN OPERATOR indentation (EDIT: Option is in Expressions/Parentheses content)

instead of
Code:

WHERE condition
           AND(condition
           OR condition)

Code:

WHERE condition
           AND(condition
                  OR condition)
Back to top
View user's profile Send private message MSN Messenger
Lxocram



Joined: 07 Jun 2007
Posts: 16
Location: Belgium, Ghent

PostPosted: Wed Mar 24, 2010 1:22 pm    Post subject: Line comment indentation Reply with quote

An option for line comment indentation
Code:

    WHERE condition --===============
 --=  Comment here
    --=  bla bla bla
 --===============                     
                OR ....


Code:

    WHERE condition
    --===============
    --=  Comment here
    --=  bla bla bla
    --===============
               OR ....



options:

    * total left
    * same as code
    * one tab left of code
    * new line (when code is folowed by -- place the comment on a new line
    * replace line comments with comment zone (multi lines of -- become /**/)
Back to top
View user's profile Send private message MSN Messenger
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Go to page Previous  1, 2, 3, 4
Page 4 of 4

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group