Postgres WITH SELECT ... is very handy but in that case I would create three views instead. It seems that wine_ratings tags and detail could be used in other queries and having the views created would prevent duplicating their code.
How I write SQL
31–40 of 84 posts
Re: How I write SQL
#32Earlier quoted context omitted.
No, no, no. Comma first variable listing is the worst thing ever in the history of the world. Having the comma in the proper place has such a negligible cost (oh no I might have to delete the comma on the last element and add it to the formerly-last element!), and having it like this looks so god awful and doesn't really save you anything if you're swapping the first element instead of the last one.
Why is "after the element" the proper place for a comma?
(I can't reply to my reply)
Comma first seems more consistent to me.... unless you write your WHERE clauses like this:
WHERE a=b AND
a=d AND
e=f
Re: How I write SQL
#33A neat trick on writing sql that I learned working with Oracle consultants: SELECT field1 , field2 , field3 , another_field FROM ... By placing the comma at the beginning of the line, instead of at the end, we can very easily reorganize the sequence without fiddling with commas most of the time: SELECT field1 , field3 , another_field , field2 FROM ...
Along the same lines it makes it easier to comment out items when debugging. Especially if you add in tricks like SELECT 0.
For example multiple lines in this query would cause an error:
SELECT a.field1,
a.field2,
-- b.field1,
-- b.field2,
c.field1,
-- b.field3
FROM a,
-- b,
c
WHERE --a.field1 = b.field1
AND a.field2 = c.field1
SELECT 0
, a.field1
, a.field2
--, b.field1
--, b.field2
, c.field1
--, b.field3
FROM dual
, a
--, b
, c
WHERE 0=0
-- AND a.field1 = b.field1
AND a.field2 = c.field1
Re: How I write SQL
#34My personal style has a lot of similarities, but with some glaring differences. The biggest is that I put the comma in front of the next item, rather than trailing the one before. What this means is that when I add a new thing to the list of columns, I'm less likely to leave one out. See http://bentilly.blogspot.com/2011/02/sql-formatting-style.ht... for what this looks like in practice.
I use this formatting style as well - it's also easier to format as the columns can all be aligned with tabs.
Re: How I write SQL
#35I wonder why so many people hate writing sql with such a poisonous intensity these days. I remember my database-systems class being one of my favorite classes when I was in school. Whats wrong with relational algebra and writing sql? I always thought they were fun.
Re: How I write SQL
#36A neat trick on writing sql that I learned working with Oracle consultants: SELECT field1 , field2 , field3 , another_field FROM ... By placing the comma at the beginning of the line, instead of at the end, we can very easily reorganize the sequence without fiddling with commas most of the time: SELECT field1 , field3 , another_field , field2 FROM ...
No, no, no. Comma first variable listing is the worst thing ever in the history of the world. Having the comma in the proper place has such a negligible cost (oh no I might have to delete the comma on the last element and add it to the formerly-last element!), and having it like this looks so god awful and doesn't really save you anything if you're swapping the first element instead of the last one.
My experience.
Changed formatting is a shock the first time. But it does not take long to retrain yourself to find it aesthetically more pleasing.
When you're making a quick fix to a batch job that does not hit the problematic query for 30 minutes, the cost of messing up the comma is 30 minutes. (Yeah, I know, it all should be properly factored out, and unit tested. But there is a lot of lightly tested code in the real world that behaves just like I said.) Because SQL is stored as plain text, there is real value in making your mental compiler have to do no work to notice stupid syntactical stuff.
Re: How I write SQL
#37A neat trick on writing sql that I learned working with Oracle consultants: SELECT field1 , field2 , field3 , another_field FROM ... By placing the comma at the beginning of the line, instead of at the end, we can very easily reorganize the sequence without fiddling with commas most of the time: SELECT field1 , field3 , another_field , field2 FROM ...
This reminds me about something that I like in C#, you can leave an extra comma at the end of a sequence and the compiler doesn't freak out on you.
Re: How I write SQL
#38The fact that the first thing he does with the tags is unnest them is, IMO, material evidence for a traditional 1NF formulation. It's worth considering that using arrays is a violation of the first normal form. That's a good indicator of how obvious Codd et. al. thought this rule was. Other than that, I use a variant of this style, where I put things on one line if possible (especially GROUP BY and ORDER BY). And I t…
I would tend to normalise arrays too, but I can see the argument that when getting an 'app' and turning it into an object in $programming_language (say), it's rather a pain to have to go through the sql result set building up an object from the denormalised data (and arguably results in a lot of wasted data being transmitted). It gets particularly bad if you have more than one 1-many relationship on the object you're trying to build up.
Re: How I write SQL
#39A neat trick on writing sql that I learned working with Oracle consultants: SELECT field1 , field2 , field3 , another_field FROM ... By placing the comma at the beginning of the line, instead of at the end, we can very easily reorganize the sequence without fiddling with commas most of the time: SELECT field1 , field3 , another_field , field2 FROM ...
This reminds me about something that I like in C#, you can leave an extra comma at the end of a sequence and the compiler doesn't freak out on you.
Python is the same way - it lets you have trailing commas for lists, tuples, and dictionaries.
Re: How I write SQL
#40Enjoy. A CTE, Xpath, and Oracle-specific hierarchical bits for a data report I'm generating. My favorite part was the XML in a CLOB column where all the values were stored in XML element attributes. I tend to build these monstrosities up in a very repl-like way. SQL actually led me to Lisp . . .
BTW, I can't imagine any of the below is legible, but I find myself enjoying these types of things.
with bgu as
(select
protocol_code,
application_code,
description,
lab,
proj."seqno",
proj."project",
proj."protocol",
'false' "nexttag",
'true' "materialtag"
from
protocol,
xmltable('for $a in /*
for $n at $nidx in //*/MATERIAL
return
{fn:data($n/@project)}
{fn:data($n/@protocol)}
'
passing xmltype(protocol.xml)
columns
"seqno" for ordinality,
"project" varchar2(100),
"protocol" varchar2(100)) proj
where
active = 'Y'
union
select
protocol_code,
application_code,
description,
lab,
proj."seqno",
proj."project",
proj."protocol",
'true' "nexttag",
'false' "materialtag"
from
protocol,
xmltable('for $a in /*
for $n at $nidx in //*/NEXT
return
{fn:data($n/@project)}
{fn:data($n/@protocol)}
'
passing xmltype(protocol.xml)
columns
"seqno" for ordinality,
"project" varchar2(100),
"protocol" varchar2(100)) proj
where
active='Y'
order by 1,2,4 )
select
*
from
(select distinct
protocol_code,
sys_connect_by_path(protocol_code,'||||'),
connect_by_isleaf "isleaf"
from bgu
where protocol_code like 'PROTNAME%'
connect by nocycle prior "protocol" = protocol_code)
where "isleaf"=1