How do you format?

[slimdave]

In the world of databases, with all their complexity, sophistication, power and flexibility, there is perhaps no more contentious subject than ... how to format SQL.

For a number of years it has been my pleasure to drive collegues crazy by insisting on such formatting as ...

Select
   t1.my_column,
   Trunc(t1.my_date,'DD')
     my_date_day,
   Sum(t2.my_number)
     sum_my_number
From
   my_table_1 t1,
   my_table_2 t2
Where
   t1.my_column = t2.my_column(+) And
   t1.my_key = 32;

... where their preference would be ...

SELECT T1.MY_COLUMN,
  TRUNC(T1.MY_DATE,'DD') MY_DATE_DAY,
   SUM(T2.MY_NUMBER) SUM_MY_NUMBER
FROM MY_TABLE_1 T1,
   MY_TABLE_2 T2
WHERE T1.MY_COLUMN = T2.MY_COLUMN(+)
AND T1.MY_KEY = 32;

... or some other illegible crap.

So here's the questions:- What do you love and hate in formatting? Are you an upper caser or a lower caser, or do you walk the fine line of mixing it up? Do you live-and-let-live, or are you a formatting nazi who has to have everything consistent? Should developers be shot?

[marist89]

personally, I like...

select t1.my_column, trunc(t1.my_date,'DD') f1, sum (t2.my_number) f2 from my_table_1 t1, my_table_2 t2 where t1.my_column = t2.my_column(+) and t1.my_key = 32;

...but then again, I think prstat is too much to type instead of top...

[slimdave]

Originally posted by marist89
personally, I like...

select t1.my_column, trunc(t1.my_date,'DD') f1, sum (t2.my_number) f2 from my_table_1 t1, my_table_2 t2 where t1.my_column = t2.my_column(+) and t1.my_key = 32;

...but then again, I think prstat is too much to type instead of top...

There is a very special place in hell reserved for the type of person that would write code like that, my friend.

[DaPi]

Jeff, I'm kind of surprised that you haven't written a perl pre-compiler that lets you use two letter abbreviations for the SQL "words":

 SE * FR mtab WH mcol = . . AN . .

UX rls O?

slimdave, how come you put column aliases on separate lines and table aliases on the same line? Is this a chink in the armour?

If you've ever worked with what MS Access produces as SQL, then ANY of the above are welcome!

You don't really want to know my prefs but . . . I like to have the ORACLE WORDS in upper case, table names in lower case and column names (which in my current db have no _ ) mixed! I also like to have the AND's at the start of line. If anything needs brackets, line them up (unless it fits on one line).

 
SELECT t1.myColumn,
       TRUNC(t1.myDate,'DD') myDateDay,
       SUM(t2.myNumber) sumMyNumber
FROM  my_table_1 t1,
      my_table_2 t2
WHERE t1.myColumn = t2.myColumn(+)
AND   t1.myKey = 32
AND   EXISTS
(SELECT *
 FROM
 WHERE . . .
 AND
 (....
  OR
  .....
 )
);

Of course I never actually format it like that in real life

[jovery]

Personally I always find it best to use any style which irritates the current development team.

Flexibility is the key to good DBA/Developer relations

[abhaysk]

I would guess there would not be more legible script than this

/*****************************************/
/*      Use this peice of code as        */
/*        standard for writing           */
/*            any script!                */
/*****************************************/

Select
   t1.my_column                         ,
   Trunc(t1.my_date,'DD') my_date_day   ,
   Sum(t2.my_number)      sum_my_number
From
   my_table_abcdef t1  ,
   my_table_gh     t2
Where
   t1.my_column  =  t2.my_column(+) And
   t1.my_key     =  32
;

What do you think Dave?

Abhay.

[DaPi]

Abhay, it looks very pretty arranged in columns (did you ever do RPG? ) Trouble is it all goes to pot when you modify it to include:

And t2.my_unbelievably_long_unexpected_column_name = . . . . .

Must say, it's a darn sight better than most code around here!

[TomazZ]

I have never really understood those Capitals.
When it's short is like this

select field1, field2 from table where field3 = value;

When it is longer it's like this

select t1.my_column,
       trunc(t1.my_date,'DD') my_date_day,
       sum(t2.my_number) sum_my_number
  from my_table_abcdef t1,
       my_table_gh t2
 where t1.my_column = t2.my_column(+)
   and t1.my_key = 32
   and exists (select * 
                 from my_exists_table
                where key = t1.my_key)

[slimdave]

Originally posted by DaPi
[B]slimdave, how come you put column aliases on separate lines and table aliases on the same line? Is this a chink in the armour?

It's just that kind of subtle inconsistency that people find annoying.

The answer of course is that you enforce a standard set of table aliases for the schema, so they don't really need to be as prominent.

I like to have the ORACLE WORDS in upper case, table names in lower case and column names (which in my current db have no _ ) mixed!

I hate that upper case stuff -- that's what drives me crazy. It's like using a ZX81 Spectrum, or some crappy old BASIC dialog.

Mixed case for the talbe and column names sounds nice.

I also like to have the AND's at the start of line.

I got into the habit of "and'ing" at the end of the line because it makes it easier to comment out a predicate when debugging, by putting "--" at the beginning of the line. I thought it would be make the code difficult to read, but it's nearly always an "And" that's used -- "Or's" tend to be in parenthetical pairs anyway, so they rather stand out. I sometimes (for variety) like to put "And" at the end of the line, and "Or" at the beginning ...

Select
   t1.my_column,
   Trunc(t1.my_date,'DD')
     my_date_day,
   Sum(t2.my_number)
     sum_my_number
From
   my_table_1 t1,
   my_table_2 t2
Where
   t1.my_column = t2.my_column(+) And
   (   t1.my_key = 32
    Or t1.my_other_key = 'W') And
   Exists
      (
      Select 1
      From   my_other_table t3
      Where  t3.my_key = t1.my_key);

[slimdave]

Originally posted by jovery
Personally I always find it best to use any style which irritates the current development team.

Flexibility is the key to good DBA/Developer relations

By flexibility, you mean enforce a different set of standards for each day of the week? I like it already!

"Sorry Bob, you know that Tuesdays code is upper case for reserved words and table names. Get it reformatted by midnight, before the mixed-case function name rule comes into effect."