Formatting requests - post them here

Refactors and formats SQL code.

Moderators: Chris Auckland, David Atkinson, Anu Deshpande, Luke Jefferson

Postby Lxocram » Fri Oct 10, 2008 3:18 pm

remove empty lines option in layout sql
Lxocram
 
Posts: 16
Joined: Thu Jun 07, 2007 2:44 pm
Location: Belgium, Ghent

Re: Feature request

Postby willspurgeon » Fri Jan 16, 2009 10:12 pm

I second this comment.

doclane wrote:Indent line comments with next or previous line
This:
Code: Select all
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: Select all
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
willspurgeon
 
Posts: 5
Joined: Fri Jan 16, 2009 8:44 pm

Re: JOIN layout and tabs vs spaces

Postby willspurgeon » Fri Jan 16, 2009 10:26 pm

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 all
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 all
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 all
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.
willspurgeon
 
Posts: 5
Joined: Fri Jan 16, 2009 8:44 pm

Postby howarthcd » Mon Jan 19, 2009 9:18 am

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
howarthcd
 
Posts: 50
Joined: Wed May 16, 2007 10:30 am

Postby CarlUman » Thu Jan 22, 2009 8:51 pm

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

"lowercase keywords? nobody need to shout at their database :) "
CarlUman
 
Posts: 1
Joined: Thu Jan 22, 2009 8:46 pm

Option to have blank line before each Join

Postby peterzeke » Tue Feb 10, 2009 4:48 pm

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 all

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!
peterzeke
 
Posts: 1
Joined: Tue Feb 10, 2009 4:20 pm

Formatting of Case statments.

Postby Paul Richmond » Wed May 13, 2009 10:52 pm

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

Currently Produces.....
Code: Select all
                           ,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: Select all
                           ,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
Paul Richmond
 
Posts: 2
Joined: Wed May 13, 2009 10:43 pm
Location: Tacoma, Washington

Long lines not 'wrapping'

Postby Paul Richmond » Wed May 13, 2009 10:57 pm

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

Code: Select all
                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
Paul Richmond
 
Posts: 2
Joined: Wed May 13, 2009 10:43 pm
Location: Tacoma, Washington

Feature request

Postby Michael.Kriegner » Wed Jun 17, 2009 11:16 am

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: Select all
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: Select all
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
Michael.Kriegner
 
Posts: 7
Joined: Tue Aug 29, 2006 6:47 pm

case statements

Postby MartinB » Fri Sep 04, 2009 10:51 am

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 all
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 all
        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 )
MartinB
 
Posts: 5
Joined: Fri Sep 04, 2009 10:42 am

Postby Anu Deshpande » Fri Sep 04, 2009 4:14 pm

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
Anu Deshpande
 
Posts: 692
Joined: Mon Apr 20, 2009 3:53 pm
Location: Cambridge

Postby MartinB » Tue Sep 08, 2009 12:55 pm

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: Select all
EXEC dbo.usp_CalledsProc @Param1, @Param2, @Param3, Param4, @Param5, @Param6,
    @Param7, @Param8, @Param9, @Param10





would become

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

MartinB
 
Posts: 5
Joined: Fri Sep 04, 2009 10:42 am

Another Layout Suggestion

Postby dbbishop » Thu Sep 10, 2009 1:20 am

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 all
SELECT ProductID,
            ProductName
FROM    Products
dbbishop
 
Posts: 11
Joined: Fri Feb 13, 2009 9:50 pm
Location: Kansas City, MO

CONDITIONAL code and BOOLEAN between parentheses

Postby Lxocram » Wed Mar 24, 2010 1:00 pm

ELSE IF as one operator
instead of:
Code: Select all
IF
...
ELSE IF
        ...
        ELSE IF
                ...
                ELSE IF

statement should look like
Code: Select all
IF
...
ELSE IF
...
ELSE IF

...
ELSE


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

instead of
Code: Select all
WHERE condition
           AND(condition
           OR condition)

Code: Select all
WHERE condition
           AND(condition
                  OR condition)
Lxocram
 
Posts: 16
Joined: Thu Jun 07, 2007 2:44 pm
Location: Belgium, Ghent

Line comment indentation

Postby Lxocram » Wed Mar 24, 2010 1:22 pm

An option for line comment indentation
Code: Select all
    WHERE condition --===============
 --=  Comment here
    --=  bla bla bla
 --===============                     
                OR ....


Code: Select all
    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 /**/)
Lxocram
 
Posts: 16
Joined: Thu Jun 07, 2007 2:44 pm
Location: Belgium, Ghent

Previous

Return to SQL Refactor 1

Who is online

Users browsing this forum: No registered users and 0 guests

cron