Being Cellfish

Stuff I wished I've found in some blog (and sometimes did)

Readable SQL

Change of Address
This blog has moved to blog.cellfish.se.

Readable SQL

  • Comments 3

I read this article on SQL Code Layout and Beautification and can only agree with the author that other people's SQL often is hard to understand because I'm so used to how I write my SQL statements. One of the links is to an on-line tool formatting your SQL according to a number of rules. I was happy to find that my way of writing SQL was supported:

SELECT a,
       b,
       c AS d
  FROM x,
       y,
       z AS w
 WHERE a = 2
   AND b IN (3,4,7)

And another example:

  SELECT obj.run,
         obj.camcol,
         STR(obj.field,3) AS field,
         STR(obj.rowc,6,1) AS rowc,
         STR(obj.colc,6,1) AS colc,
         STR(dbo.FOBJ(obj.objid),4) AS id,
         STR(obj.psfmag_g - 0 * obj.extinction_g,6,3) AS g,
         STR(obj.psfmag_r - 0 * obj.extinction_r,6,3) AS r,
         STR(obj.psfmag_i - 0 * obj.extinction_i,6,3) AS i,
         STR(obj.psfmag_z - 0 * obj.extinction_z,6,3) AS z,
         STR(60 * distance,3,1) AS d,
         dbo.FFIELD(neighborobjid) AS nfield,
         STR(dbo.FOBJ(neighborobjid),4) AS nid,
         'new' AS 'new'
    FROM (SELECT obj.objid,
                 run,
                 camcol,
                 field,
                 rowc,
                 colc,
                 psfmag_u,
                 extinction_u,
                 psfmag_g,
                 extinction_g,
                 psfmag_r,
                 extinction_r,
                 psfmag_i,
                 extinction_i,
                 psfmag_z,
                 extinction_z,
                 nn.neighborobjid,
                 nn.distance
            FROM photoobj AS obj
                 JOIN neighbors AS nn
                   ON obj.objid = nn.objid
           WHERE 60 * nn.distance BETWEEN 0 AND 15
             AND nn.mode = 1
             AND nn.neighbormode = 1
             AND run = 756
             AND camcol = 5
             AND obj.TYPE = 6
             AND (obj.flags & 0x40006) = 0
             AND nchild = 0
             AND obj.psfmag_i < 20
             AND (g - r BETWEEN 0.3 AND 1.1
                  AND r - i BETWEEN -0.1 AND 0.6)) AS obj
         JOIN photoobj AS nobj
           ON nobj.objid = obj.neighborobjid
   WHERE nobj.run = obj.run
     AND (ABS(obj.psfmag_g - nobj.psfmag_g) < 0.5
           OR ABS(obj.psfmag_r - nobj.psfmag_r) < 0.5
           OR ABS(obj.psfmag_i - nobj.psfmag_i) < 0.5)
ORDER BY obj.run,
         obj.camcol,
         obj.field

  • And if you listened to me and started to write nice looking SQL , maybe you wanna look ate making your

  • Funny that you should post about formatting SQL, yet your code is poorly formatted..

  • @Ryan: Which code is poorly formatted? The examples? Something else?

Page 1 of 1 (3 items)