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.

Sorting By “Something Else”

I have a period calendar table where the period names are P01, P02, and so on. The years are fiscal years 2009, 2010, 2011, and on from there as you would expect. The user expects to pick a period and year combination. However, they want to see it in that order… period, and then year. As a designer I can easily combine the two values together in the format PPP YYYY with a concatenation operation. But then the LOV displays in alphabetical rather than chronological order. So I see this:

P01 2008
P01 2009
P01 2010
P01 2011
P02 2008
P02 2009
…

Instead of this:

P01 2008
P02 2008
P03 2008
…
P01 2009
P02 2009
P03 2009
…

This is not what the user expects or requires, but is easily solved by editing the LOV query and adding a custom ORDER BY clause like this:

select period || ' ' || year
from fiscal_calendar
order by period_start_date

Sorting by the period start date would cause the alphabetical list to be sorted chronologically instead. However, doing any sort of manual editing – even in a simple LOV query – is something I want to avoid. Any time I have to click the “do not regenerate SQL” option it leaves me open for problems later on. I could add the start date to my query and sort it normally. However, I don’t want to do that as it would clutter the display.

Setting SORT_BY_NO=NO

This parameter is found in the .PRM file that belongs to the database engine referenced by a universe. In the old days the format of the .PRM file was the same as that found in a Windows .INI file. Today they use an XML structure instead. By default the SORT_BY_NO parameter is set to YES, so the line in the file looks like this:

<Parameter Name="SORT_BY_NO">YES</Parameter>

Clear as mud, yes? no? 😆 What it means is that yes, it is true; I cannot sort by something that does not appear in the select clause.

First I need to determine if my database allows me to sort by a column that does not appear in the select; Oracle and Teradata both do and I imagine others do as well. Changing this parameter won’t do me any good if the database does not support the technique. Next, I can find this file on my computer where I have Designer installed. The actual location will vary based on the installed path. The file name will be DBNAME.PRM, or in my case teradata.prm. I opened the file with a simple text editor, found the line shown above, and changed the value from YES to NO. It’s now a double-negative. It says, if I can paraphrase:

“It is NOT TRUE that I CANNOT sort by something NOT in the select”

or rather

“I is TRUE that I CAN sort by something NOT in the select”

Very clear, I am sure.

The Results

Before this change was made the “manage sorts” button on the query panel for editing LOV definitions in Designer was never available. After making this change, saving the file, and restarting Designer, I can now click this button.

When I click that button I get a list of objects from my universe. These objects do not have to appear in the select clause but can now appear in the sort clause. Problem solved.

But only after I set SORT_BY_NO equal to NO rather than YES in my parameter file.

By finally writing this down as a blog post, I hope that I will remember this the next time I get a new laptop and won’t have to spend time searching for the parameter setting that allows me to do this. I hope it helps someone else as well, but mainly this one is just for me. It happens that way sometimes. 😎