While I do think SQL leaves a number of things to desire, I don't see much in the objections you raise here.
Some items have already been adressed by @geraldew
, I just wanted to add:
6. No variables in ANSI SQL
There is a good reason for that - variables are an imperative device and in many cases using them would force a definite execution order, which makes it harder (impossible) to optimize the query. This is not unique to SQL, you see this in functional languages as well.
For the same reason, if your SQL dialect lets you use user-defined functions, then they should typically be free of side-effects. If they are not, unexpected (bad) things will happen.
Now, about your example, you wrote:
(publish_year / 100) as century,
case
when century < 0 then (century * -1) || ' B.C.'
when century > 0 then century || ' A.D.'
as formatted_century
It now looks like you would need to duplicate the publish_year/100 twice, and this is what you would solve with a variable.
Now, typically you'd want to push this type of formatting to a reporting tool. But if you really want to do it in a query, you'd simply use a built-in date formatting function. And, you'd make your life considerably easier by storing the publish_year as a date.
With that in place, the century expression would become (showing postgres examples):
to_char(publish_date, 'CC')
and
abs(cast(to_char(publish_date, 'CC') as int))||' '||to_char(publish_date, 'B.C.')
Now, you could still nitpick over the duplication of the 'CC' formatting of the publish_date but the point is that usually when people say they need variables in SQL, they really don't, they just need to use built-in functions to do the job for you.
(see: postgresql.org/docs/8.2/functions-...)
Now, typically you'd want to push this type of formatting to a reporting tool. But if you really want to do it in a query, you'd simply use a built-in date formatting function. And, you'd make your life considerably easier by storing the publish_year as a date.
With respect, this is a classic example of "this isn't the product's problem, it's the user's problem." Yes, of course - users have been dealing with this and using workaround for years now. My question is - can this be better?
variables are an imperative device and in many cases using them would force a definite execution order, which makes it harder (impossible) to optimize the query
You're saying it's impossible to have some sense of variables in SQL, with some sane guardrails, and be able to optimize a query? I doubt that, given that there are SQL dialects that have variables.
If dividing one number by another static number is an execution that must be done before others, I think that's a fine tradeoff for most/all users. There must be a better way.
For further actions, you may consider blocking this person and/or reporting abuse
We're a place where coders share, stay up-to-date and grow their careers.
While I do think SQL leaves a number of things to desire, I don't see much in the objections you raise here.
Some items have already been adressed by @geraldew , I just wanted to add:
6. No variables in ANSI SQL
There is a good reason for that - variables are an imperative device and in many cases using them would force a definite execution order, which makes it harder (impossible) to optimize the query. This is not unique to SQL, you see this in functional languages as well.
For the same reason, if your SQL dialect lets you use user-defined functions, then they should typically be free of side-effects. If they are not, unexpected (bad) things will happen.
Now, about your example, you wrote:
It now looks like you would need to duplicate the publish_year/100 twice, and this is what you would solve with a variable.
Now, typically you'd want to push this type of formatting to a reporting tool. But if you really want to do it in a query, you'd simply use a built-in date formatting function. And, you'd make your life considerably easier by storing the publish_year as a date.
With that in place, the century expression would become (showing postgres examples):
to_char(publish_date, 'CC')
and
abs(cast(to_char(publish_date, 'CC') as int))||' '||to_char(publish_date, 'B.C.')
Now, you could still nitpick over the duplication of the 'CC' formatting of the publish_date but the point is that usually when people say they need variables in SQL, they really don't, they just need to use built-in functions to do the job for you.
(see: postgresql.org/docs/8.2/functions-...)
Thanks for responding, @rolandbouman
With respect, this is a classic example of "this isn't the product's problem, it's the user's problem." Yes, of course - users have been dealing with this and using workaround for years now. My question is - can this be better?
You're saying it's impossible to have some sense of variables in SQL, with some sane guardrails, and be able to optimize a query? I doubt that, given that there are SQL dialects that have variables.
If dividing one number by another static number is an execution that must be done before others, I think that's a fine tradeoff for most/all users. There must be a better way.