| Author |
Message |
jbrown
Joined: 13 Sep 2008
Posts: 12
|
 Posted: Tue Sep 16, 2008 6:23 pm Post subject: "spaces to tabs" and lowercase keywords |
 |
|
I've seen this here already, but wanted to give a little shout out for:
1. tabs. a textpad-like "spaces to tabs" at the current tab setting (2,4, 8...)
2. how about lowercase keywords? nobody need to shout at their database 
thanks, great product! |
|
| Back to top |
|
 |
Lxocram
Joined: 07 Jun 2007
Posts: 8
Location: Belgium, Ghent
|
| Posted: Fri Oct 10, 2008 2:18 pm Post subject: |
|
|
| remove empty lines option in layout sql |
|
| Back to top |
|
|
willspurgeon
Joined: 16 Jan 2009
Posts: 2
|
| Posted: Fri Jan 16, 2009 9:12 pm Post subject: Re: Feature request |
|
|
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 |
|
|
willspurgeon
Joined: 16 Jan 2009
Posts: 2
|
| Posted: Fri Jan 16, 2009 9:26 pm Post subject: Re: JOIN layout and tabs vs spaces |
|
|
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 |
|
|
howarthcd
Joined: 16 May 2007
Posts: 32
|
| Posted: Mon Jan 19, 2009 8:18 am Post subject: |
|
|
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 |
|
|
CarlUman
Joined: 22 Jan 2009
Posts: 1
|
| Posted: Thu Jan 22, 2009 7:51 pm Post subject: |
|
|
#1 for me has already been asked for a number of times...
"lowercase keywords? nobody need to shout at their database " |
|
| Back to top |
|
|
peterzeke
Joined: 10 Feb 2009
Posts: 1
|
| Posted: Tue Feb 10, 2009 3:48 pm Post subject: Option to have blank line before each Join |
|
|
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 |
|
|
Paul Richmond
Joined: 13 May 2009
Posts: 2
Location: Tacoma, Washington
|
| Posted: Wed May 13, 2009 9:52 pm Post subject: Formatting of Case statments. |
|
|
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 |
|
|
Paul Richmond
Joined: 13 May 2009
Posts: 2
Location: Tacoma, Washington
|
| Posted: Wed May 13, 2009 9:57 pm Post subject: Long lines not 'wrapping' |
|
|
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 |
|
|
Michael.Kriegner
Joined: 29 Aug 2006
Posts: 7
|
| Posted: Wed Jun 17, 2009 10:16 am Post subject: Feature request |
|
|
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 |
|
|
MartinB
Joined: 04 Sep 2009
Posts: 5
|
| Posted: Fri Sep 04, 2009 9:51 am Post subject: case statements |
|
|
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 |
|
|
Anu Deshpande
Joined: 20 Apr 2009
Posts: 415
Location: Cambridge
|
| Posted: Fri Sep 04, 2009 3:14 pm Post subject: |
|
|
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 |
|
|
MartinB
Joined: 04 Sep 2009
Posts: 5
|
| Posted: Tue Sep 08, 2009 11:55 am Post subject: |
|
|
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 |
|
|
dbbishop
Joined: 13 Feb 2009
Posts: 7
Location: Kansas City, MO
|
| Posted: Thu Sep 10, 2009 12:20 am Post subject: Another Layout Suggestion |
|
|
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 |
|
|
Lxocram
Joined: 07 Jun 2007
Posts: 8
Location: Belgium, Ghent
|
| Posted: Wed Mar 24, 2010 12:00 pm Post subject: CONDITIONAL code and BOOLEAN between parentheses |
|
|
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 |
|
|
|
