Formatting requests - post them here

Refactors and formats SQL code.

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

operator placement , new option

Postby WeeeBob » Thu Jun 14, 2007 5:12 pm

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 all
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 all
SELECT
    (grossSalary+2000)/100
FROM
    personnel
WHERE
    (
     empType='m'
     OR
     empType='n'
    )
    AND
    (grossSalary-@minSalary)>0
WeeeBob
 
Posts: 12
Joined: Thu Jun 14, 2007 4:16 pm

A Different Approach ...

Postby Wyatt70 » Mon Jun 25, 2007 5:39 pm

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.
Wyatt70
 
Posts: 36
Joined: Fri Jul 29, 2005 6:17 pm
Location: Yonkers, NY

Better handling of FETCH

Postby gfrizzle » Tue Jun 26, 2007 4:04 pm

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

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


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

Code: Select all
FETCH NEXT
   FROM myCursor
   INTO
      @myVar1,
      @myVar2,
      @myVar3
gfrizzle
 
Posts: 2
Joined: Tue Jun 26, 2007 3:40 pm

Re:

Postby Baldy » Wed Aug 15, 2007 9:34 am

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
Baldy
 
Posts: 2
Joined: Wed Aug 15, 2007 9:32 am

Custom indentation rules

Postby Baldy » Wed Aug 15, 2007 9:51 am

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

Code: Select all
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
Baldy
 
Posts: 2
Joined: Wed Aug 15, 2007 9:32 am

begin / end

Postby Henrik Staun Poulsen » Thu Sep 13, 2007 10:18 am

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.
Henrik Staun Poulsen
 
Posts: 10
Joined: Mon Jan 15, 2007 9:29 am

Postby bogieman » Thu Sep 20, 2007 3:57 pm

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

my code:
Code: Select all
,   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: Select all
,       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
bogieman
 
Posts: 2
Joined: Thu Aug 30, 2007 9:03 pm

Still waiting for "Lowercase keywords" option....

Postby mmackay » Wed Sep 26, 2007 8:02 pm

...just like the "Uppercase keywords" option. Thanks.
mmackay
 
Posts: 2
Joined: Thu Aug 09, 2007 5:27 pm

BEGIN / END layout

Postby Wislon32 » Tue Oct 02, 2007 12:16 pm

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: Select all
IF....BEGIN
    other code here
END

or

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

Apart from the fact that it uses less horizontal space!
Wislon32
 
Posts: 1
Joined: Tue Oct 02, 2007 12:09 pm

IF EXISTS format suggestion

Postby p held » Wed Oct 24, 2007 10:41 pm

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.
p held
 
Posts: 3
Joined: Wed Oct 24, 2007 10:20 pm

Boolean Operator Alignment

Postby Mattigans » Thu Oct 25, 2007 3:13 pm

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 all
SELECT  *
FROM    Game2
WHERE   Winner = 'Rockies'
        AND ScoreCol = 7
        AND ScoreBos = 2


We can do this:
Code: Select all
SELECT  *
FROM    Game2
WHERE   Winner = 'Rockies'
AND     ScoreCol = 7
AND     ScoreBos = 2


I hope we're not the only ones formatting this way!
Mattigans
 
Posts: 1
Joined: Thu Oct 25, 2007 2:00 pm

Request

Postby dbarchitect » Thu Apr 03, 2008 6:15 pm

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 all
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 all
SELECT
     [Field1Alias]            = t1.[Field1]
   , [Field2Alias]            = t1.[Field2]
   , [Field3AliasLongName]    = t1.[Field3]
   , [Field4AliasLongName]    = t1.[Field4]
FROM [dbo].[Table]
WHERE [Field10] = 'Something'

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

... INSERTs from this ...
Code: Select all
INSERT INTO ([Field1], [Field2], [Field3], [Field4]) SELECT S1.Field1, S1.Field1, S1.Field1, S1.Field1 FROM [dbo].[SourceTable] AS S1

... to this ...
Code: Select all
INSERT INTO (
    [Field1]
  , [Field2]
  , [Field3]
  , [Field4])
SELECT
    [Field1]   = S1.[SourceField1]
  , [Field2]   = S1.[SourceField2]
  , [Field3]   = S1.[SourceField3]
  , [Field4]   = S1.[SourceField4]
FROM [dbo].[SourceTable] AS S1
dbarchitect
 
Posts: 2
Joined: Thu Apr 03, 2008 5:40 pm
Location: Lindon, Utah

Reverse Lay-Out SQL

Postby sager » Fri May 09, 2008 10:44 am

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.
sager
 
Posts: 1
Joined: Fri May 09, 2008 10:21 am

Postby JimF » Tue Jul 08, 2008 8:18 pm

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.
JimF
 
Posts: 20
Joined: Tue Jul 08, 2008 7:55 pm

"spaces to tabs" and lowercase keywords

Postby jbrown » Tue Sep 16, 2008 7:23 pm

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!
jbrown
 
Posts: 12
Joined: Sat Sep 13, 2008 2:05 am

PreviousNext

Return to SQL Refactor 1

Who is online

Users browsing this forum: No registered users and 1 guest