3 ms·
> Perhaps you are thinking of trying to use an output column name in a WHERE clause? Yeah, I think I am thinking about this. Its pretty frustrating because th
by nicoburns 6y ago
> Perhaps you are thinking of trying to use an output column name in a WHERE clause?
Yeah, I think I am thinking about this.
Its pretty frustrating because the actual query engine is perfectly capable of executing fairly complex SQL expressions efficiently (they're not really that complex computationally, but they are syntactically because of SQL's verbosity), but the code becomes quite unmaintainable if you use too many of them.
For example, the following expression:
ROUND((
(EXTRACT(EPOCH FROM (s.end_time - s.start_time)) / 60) -- Shift length in minutes
- (FLOOR((EXTRACT(EPOCH FROM (s.end_time - s.start_time)) / 60) / 380) * 20) -- Break length in minutes
)::numeric / 60 , 2) as hours_planned,
It repeats the sub-expression `EXTRACT(EPOCH FROM (s.end_time - s.start_time)) / 60)`. If I could name that sub expression and reference it multiple times then the overall expression would be a lot more readable.