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, 5  Next
Author Message
Lumbago



Joined: 22 May 2006
Posts: 25

PostPosted: Tue Jun 12, 2007 9:24 am    Post subject: Reply with quote

I would *very much* appreciate the possibility to force an indentation to be what you have set it to be. I have set my indentation setting to insert tabs as spaces and one tab = 2 spaces and to wrap text at 160 characters. However lay out sql gives me these to statements which to me makes no sense:
Code:

    FROM
      view1 o,
      view2 a
    WHERE
      o.period = YEAR(GETDATE() - (DATEPART(DD, GETDATE()) + 1)) * 100 + DATEPART(mm,
                                                                                  GETDATE() - (DATEPART(DD, GETDATE())
                                                                                               + 1))
      AND o.period = a.period


  UPDATE
    table1
  SET
    average_balance = (
                       SELECT
                        SUM(Final_Balance) / DATEPART(mm, GETDATE() - (DATEPART(DD, GETDATE()) + 1))
                       FROM
                        table2 m2
                       WHERE
                        period BETWEEN YEAR(GETDATE() - (DATEPART(DD, GETDATE()) + 1)) * 100 + 1
                               AND     YEAR(GETDATE() - (DATEPART(DD, GETDATE()) + 1)) * 100 + DATEPART(mm, GETDATE() - (DATEPART(DD, GETDATE()) + 1))

Both of these are from the same procedure and are laid out at the same time. In the fiste statement the o.period = YEAR(... is wrapped two times and indented like crazy but the last where-statement in the sub-query isn't wrapped at all. I'd also think that the indentation setting would place the last sub-select one tab (2 spaces in my case) from "average_balance" while it is actually aligned with the opening parenthesis. The indentation in the subselect is also only one character...
Back to top
View user's profile Send private message
WeeeBob



Joined: 14 Jun 2007
Posts: 12

PostPosted: Thu Jun 14, 2007 5:12 pm    Post subject: operator placement , new option Reply with quote

I love this software, but I really like my operators to have a line all to themselves, only option currently is to place operator at start or end of the line. A third option to 'Place Operator on line by itself' would be nice for me.

Place operator at start looks like this:
Code:
SELECT
    (grossSalary+2000)/100
FROM
    personnel
WHERE
    (
     empType='m'
     OR empType='n'
    )
    AND (grossSalary-@minSalary)>0


Place operator on line by itself would look like this:
Code:
SELECT
    (grossSalary+2000)/100
FROM
    personnel
WHERE
    (
     empType='m'
     OR
     empType='n'
    )
    AND
    (grossSalary-@minSalary)>0
Back to top
View user's profile Send private message
Wyatt70



Joined: 29 Jul 2005
Posts: 36
Location: Yonkers, NY

PostPosted: Mon Jun 25, 2007 5:39 pm    Post subject: A Different Approach ... Reply with quote

I think you would be better off if you had some kind of configuration file that we could edit to suit our purposes. I'd rather just customize my template the way I see fit than make all these posts to a forum and hope that enough people agree with me.
Back to top
View user's profile Send private message
gfrizzle



Joined: 26 Jun 2007
Posts: 2

PostPosted: Tue Jun 26, 2007 4:04 pm    Post subject: Better handling of FETCH Reply with quote

I would like to see FETCH statements handled better. Right now, they appear on a single line:

Code:
FETCH NEXT FROM myCursor INTO @myVar1, @myVar2, @myVar3


But I would expect the option to lay it out something like this:

Code:
FETCH NEXT
   FROM myCursor
   INTO
      @myVar1,
      @myVar2,
      @myVar3
Back to top
View user's profile Send private message
Baldy



Joined: 15 Aug 2007
Posts: 2

PostPosted: Wed Aug 15, 2007 9:34 am    Post subject: Re: Reply with quote

almaz wrote:
In our company we are using lowercase SQL syntax. It would be nice to have an option to convert SQL statements to lowercase.


I definitely need the lowercase option as well
Back to top
View user's profile Send private message
Baldy



Joined: 15 Aug 2007
Posts: 2

PostPosted: Wed Aug 15, 2007 9:51 am    Post subject: Custom indentation rules Reply with quote

We have a standard to indent keywords of a statement as in

Code:
select ...
  from...
  where
    and
     or


It just makes for more readable code.

But since everyone has their own standards, it would be nice to set the indentation rules per keyword
Back to top
View user's profile Send private message
Henrik Staun Poulsen



Joined: 15 Jan 2007
Posts: 10

PostPosted: Thu Sep 13, 2007 10:18 am    Post subject: begin / end Reply with quote

Boldi says:
<<I would like support for the layout described by Ken Henderson in "The Guruís Guide to Transact-SQL".

I would like to second that.
Back to top
View user's profile Send private message
bogieman



Joined: 30 Aug 2007
Posts: 2

PostPosted: Thu Sep 20, 2007 3:57 pm    Post subject: Reply with quote

1. the ability to select tabs instead of spaces for indentation
2. case statement handle the when/then like a begin

my code:
Code:
,   case
   when dbo.dfh_v_Invoice_Memo.rectype = 'detail' then
      case when dbo.dfh_v_Invoice_Memo.source = 'erp' then
         case when dbo.dfh_v_Invoice_Memo.doctype = 'invoice' then
            case when isnull(dbo.ar_ivdtl_tbl.ar_ivdtl_prcuom, '') = '' then
               dbo.so_prod_tbl.en_uom_pricedef
            else
               dbo.ar_ivdtl_tbl.ar_ivdtl_prcuom
            end
         else
            case
            when isnull(dbo.ar_dbcrm_tbl.ar_dbcrm_prcuom, '') = '' then
               dbo.so_prod_tbl.en_uom_pricedef
            else
               dbo.ar_dbcrm_tbl.ar_dbcrm_prcuom
            end
         end
      when dbo.dfh_v_Invoice_Memo.source = 'iave' then
         dbo.so_prod_tbl.en_uom_pricedef
      else
         ''
      end
   else
      ''
   end


Refactor:

Code:
,       case when dbo.dfh_v_Invoice_Memo.rectype = 'detail' then case when dbo.dfh_v_Invoice_Memo.source = 'erp' then case when dbo.dfh_v_Invoice_Memo.doctype = 'invoice' then case when isnull(dbo.ar_ivdtl_tbl.ar_ivdtl_prcuom, '') = '' then dbo.so_prod_tbl.en_uom_pricedef
                                                                                                                                                                                     else dbo.ar_ivdtl_tbl.ar_ivdtl_prcuom
                                                                                                                                                                                end
                                                                                                                           else case when isnull(dbo.ar_dbcrm_tbl.ar_dbcrm_prcuom, '') = '' then dbo.so_prod_tbl.en_uom_pricedef
                                                                                                                                     else dbo.ar_dbcrm_tbl.ar_dbcrm_prcuom
                                                                                                                                end
                                                                                                                      end
                                                                      when dbo.dfh_v_Invoice_Memo.source = 'iave' then dbo.so_prod_tbl.en_uom_pricedef
                                                                      else ''
                                                                 end
             else ''
        end
Back to top
View user's profile Send private message
mmackay



Joined: 09 Aug 2007
Posts: 2

PostPosted: Wed Sep 26, 2007 8:02 pm    Post subject: Still waiting for "Lowercase keywords" option.... Reply with quote

...just like the "Uppercase keywords" option. Thanks.
Back to top
View user's profile Send private message
Wislon32



Joined: 02 Oct 2007
Posts: 1

PostPosted: Tue Oct 02, 2007 12:16 pm    Post subject: BEGIN / END layout Reply with quote

I like and would prefer DRBuckingham's post regarding layout of BEGIN and END as (using IF as an example, but generalised to ALL BEGIN.END
Code:
IF....BEGIN
    other code here
END

or

IF......
BEGIN
    other code here
END

Apart from the fact that it uses less horizontal space!
Back to top
View user's profile Send private message
p held



Joined: 24 Oct 2007
Posts: 3

PostPosted: Wed Oct 24, 2007 10:41 pm    Post subject: IF EXISTS format suggestion Reply with quote

I would like an option to force a simple IF EXISTS() condition to ONE line.
Example:

Where
exists ( Select
*
from
@TMMSale T
Where
T.MaxAkey=D.a_Key )
and D.a_Type='I'

Instead, I would prefer the following:
Where
exists ( Select * from @TMMSale T Where T.MaxAkey=D.a_Key )
and D.a_Type='I'

I prefer this because my if exists clauses are typically short and simple and spreading it out across multiple lines seems unwarranted.

I would have figured that the option on "Subquery placement" would apply to an "If exists" clause as well.

thanks in advance.
Back to top
View user's profile Send private message
Mattigans



Joined: 25 Oct 2007
Posts: 1

PostPosted: Thu Oct 25, 2007 3:13 pm    Post subject: Boolean Operator Alignment Reply with quote

Great to see so many different ideas here, knowing that the dev team is considering this stuff.

Count me in for tabs- e.g. Convert every x spaces to tab, and use tabs for alignment. Seems like we'll be seeing that in a future release for sure.

How about this... an option to align boolean operators with the previous line, so instead of:


Code:
SELECT  *
FROM    Game2
WHERE   Winner = 'Rockies'
        AND ScoreCol = 7
        AND ScoreBos = 2


We can do this:
Code:

SELECT  *
FROM    Game2
WHERE   Winner = 'Rockies'
AND     ScoreCol = 7
AND     ScoreBos = 2


I hope we're not the only ones formatting this way!
Back to top
View user's profile Send private message
dbarchitect



Joined: 03 Apr 2008
Posts: 2
Location: Lindon, Utah

PostPosted: Thu Apr 03, 2008 6:15 pm    Post subject: Request Reply with quote

There was a suggestion earlier to take out keywords like INTO for INSERT INTO and FROM in the DELETE FROM. I wouldn't want this unless it was a configured option (obviously). I love the extra keyword syntax because it offers joins right off the FROM clause (on the delete) to be properly handled and less skilled DBAs can quickly see the difference (more legible).

Suggestions are:
1) Indent the assignments, data types, or other "columns"
2) Include the column name from the schema as an alias on the field automatically
3) Format the alias to be either at the front of the column line [Alias1] = [Field1] or at the end [Field1] AS [Alias1]
4) Align the alias by = or AS keyword
5) these would also be available in the action queries, INSERT, UPDATE, or other situations where assignments are done, aligning them as a column where MAXLEN(Column1) + Tab + " = " + Columns2

Examples here:

Code:
SELECT t1.[Field1],  t1.[Field2], t1.[Field3] AS Field3Alias, Field4Alias = t1.[Field4]
FROM [dbo].[Table] as T1
WHERE t1.[Field10] = 'Something'

... looks like this ...
Code:
SELECT
     [Field1Alias]            = t1.[Field1]
   , [Field2Alias]            = t1.[Field2]
   , [Field3AliasLongName]    = t1.[Field3]
   , [Field4AliasLongName]    = t1.[Field4]
FROM [dbo].[Table]
WHERE [Field10] = 'Something'

.. or this ...
Code:
SELECT
     [Field1]         AS [Field1Alias]      
   , [Field2]         AS [Field2Alias]
   , [Field3LongName] AS [Field3AliasLongName]
   , [Field4]         AS [Field4AliasLongName]
FROM [dbo].[Table]
WHERE [Field10] = 'Something'

... INSERTs from this ...
Code:

INSERT INTO ([Field1], [Field2], [Field3], [Field4]) SELECT S1.Field1, S1.Field1, S1.Field1, S1.Field1 FROM [dbo].[SourceTable] AS S1

... to this ...
Code:
INSERT INTO (
    [Field1]
  , [Field2]
  , [Field3]
  , [Field4])
SELECT
    [Field1]   = S1.[SourceField1]
  , [Field2]   = S1.[SourceField2]
  , [Field3]   = S1.[SourceField3]
  , [Field4]   = S1.[SourceField4]
FROM [dbo].[SourceTable] AS S1
Back to top
View user's profile Send private message
sager



Joined: 09 May 2008
Posts: 1

PostPosted: Fri May 09, 2008 10:44 am    Post subject: Reverse Lay-Out SQL Reply with quote

Since I often copy sql code back and forth from VBA scripts, I frequently use Ctrl-B + Ctrl-L. It comes in one long line, and it would be perfect if there was a posssibility to get my whole sql code back into one long line after I have made my changes. This would make it easier to copy back into other systems.

Best regards
Soren.
Back to top
View user's profile Send private message
JimF



Joined: 08 Jul 2008
Posts: 7

PostPosted: Tue Jul 08, 2008 8:18 pm    Post subject: Reply with quote

Hello,

I would like to make two suggestions.
1. As suggested by Mattigans before, I too would like to see logical operators line up under the "Where" statement.

2. How about Pascal Case for keywords?. I realize that this may require a dictionary to make it work on a user-by-user basis, but being a developer as much as a DBA I don't really care for all lowercase or all uppercase keywords. To me, "Set NoCount On" or "Select...From...Where...etc" is much more readable. If I was not using SSMS with its color coding of keywords, I might feel different, but with the colors I don't feel that I need the casing too.

Thanks for listening.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Go to page Previous  1, 2, 3, 4, 5  Next
Page 3 of 5

 
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