Mar 04 2010

SORT_BY_NO=NO? Very Confusing…

Categories: Universe Design Dave Rathbun @ 1:33 pm

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.

toolbar image

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. 😎

9 Responses to “SORT_BY_NO=NO? Very Confusing…”

  1. Comment by Mark

    Fantastic post Dave,

    I didn’t know this was possible, thanks for sharing. The paramters whether they are, universe or prm, based have always been poorly documented and a bit of a “Dark Art”.

    Cheers,

    Mark.

  2. Comment by kay

    Nice …after reading it I went & checked my .PRM files and found that for tredata by deafult it was set to YES but for Oracle by default it was set to NO. I’m assuming that’s the case after standard set-up. In any case excellent post Dave (as always) 🙂

  3. Comment by Chris

    SORT_BY_NO could be thought of as “Sort by Named Objects” as in “Named in the Select”

  4. Comment by Ferdie

    ahh….but order by 1,2 is great when troubleshooting and playing around with sql in design or early devleopment stage.

  5. Comment by yingchai

    But Dave, I still not really understand your post as I’m quite new to BOBJ. I have a problem while trying to sort the month members order. When I pull the month dimensions to my webi report, the month members will be displayed as; Apr, Aug, Dec, Feb, Jan, Jul, Jun, Mar, May, Nov, Oct, Sep.

    How can we configure this order from the Universe so that when users pull the month members each time, it’ll be displayed according to calendar year (starting from jan…to dec)

    Thanks.

  6. Comment by Dave Rathbun

    Hi, yingchai, unfortunately this post has nothing to do with sorting month names in chronological order. There are a number of posts on BOB that detail various suggestions on how to accomplish that. At some point I might get time to post a post here specifically for that purpose.

    This post was only about whether Business Objects will rely on the “distinct” operation on the database to provide a default sort, or whether an ORDER BY clause will be explicitly added to the LOV query instead.

  7. Comment by Kiran

    Hi Dev could you please post topic on report bursting with prompts.

    Thanks in advance.

    Regards,
    kiran.

  8. Comment by sam

    thanks Dave for posting the valuable information. It worked for me in DB2 as well.

  9. Comment by Siraj

    Hello Dave.
    I tried to use this option for sorting the to_char(date,’mm/dd/yyyy’) in the date format. However, it is removing the distinct keyword from the SQL and give repetitive LOV’s.

    Thanks.