Dave's Adventures in Business Intelligence

This has to be the parameter with the worst. Name. Ever. But let me start at the beginning.

Some databases require you to use actual column names in an ORDER BY clause. Like this:

select first_name, last_name, phone
from employee
order by last_name, first_name

Other databases let you take a shorter approach and sort by the position of the column in the select clause, Like this:

select first_name, last_name, phone
from employee
order by 2, 1

To be honest, I don’t like the shortcut. I would rather see explicit column names in my order by because that way I know exactly what is being sorted without having to refer back to the select clause. Another advantage is that if the objects in my select ever change, my order by is not affected.

There is a parameter found in the .PRM file for each database named SORT_BY_NO. When you see that name, what do you think it is? Every time I see it I assume that it is used to determine whether the SQL will contain numbers in the ORDER BY clause like order by 2, 1 instead of order by last_name, first_name. But that’s not what it does at all. Instead of doing what I described above, this parameter is used to determine if a query can be sorted by a column that does not appear in the select clause. That makes sense, doesn’t it? 🙄 It should be called SORT_BY_IN_SELECT or something. But it’s not, and here’s how it works. Continue reading “SORT_BY_NO=NO? Very Confusing…”