Re: How to order by non database columns in forms?

<oracleguru_at_lycos.com> wrote in message news:1109946303.415291.143240_at_z14g2000cwz.googlegroups.com...
> Ouch !!!
>
> There is a TSUNAMI of bad behavior in this newsgroup for quite sometime
> from a very few participants. I don't frequent this news group as much
> as I used to do.
>
> The reply begs for lack of intuition, interpretation, inference and
> deduction of the question on the part of DA Morgan. That is what we
> are supposed to learn in college, and he is a college Instructor ? Dah
> ?
>
> I perfectly understood your question and it does not require me
> to know the version of Oracle forms etc. Unfortunately, I do not have
> an answer right now. I tried that long time ago and I do not remember
> if I succeded or failed. If I recall, you can order only by database
> table columns and not even by :block_name.column_name, but I could be
> wrong.
>
> Anyway, try your queries at
> http://forums.oracle.com/forums/forum.jsp?forum=82 for forms questions.
> There are other forums there too spanning different Oracle topics.
> You may have to become member of Oracle Technology Network, which is
> free. Employees who work for oracle and other participants will
> courteously answer your questions.
>
> Oracleguru
>
> DA Morgan wrote:
>> anil.lohithan_at_gmail.com wrote:
>>
>> > Hi,
>> >
>> > I'm having a problem in order by non database columns in oracle
> forms.
>> >
>> > Anybody have any solution for the same.
>> >
>> > TIA,
>> > Anil
>>
>> Sure do. Pour high grade aviation fuel on your keyboard and apply a
>> car flare after lighting it.
>>
>> Oh about your problem ... reread you post ... what problem? With what
>> version of Oracle Forms? With what error message?
>> --
>> Daniel A. Morgan
>> University of Washington
>> damorgan_at_x.washington.edu
>> (replace 'x' with 'u' to respond)
>

ORDER BY is part of the SQL statement, which is processed by the database server.

So, regardless of the tool or version, ORDER BY can only act on data that are available to the server at the time it executes the statement. This eliminates the possibility of using non-database Forms items for sorting, since their values are not calculated until after the query is processed by the server.

If you want the effect of sorting by non-database items, then you need to construct an ORDER BY clause that includes the same logic used to populate the the non-database item. This is fairly simple if the non-database item is populated by a calculation (ie. :sal + nvl(:comm) -- but you must keep in mind that the ORDER BY clause is processed by the database server and has no access to Forms items and variables -- it can only work with stuff that's in the database or static values that are passed to it. So if the calculation is as noted above, the ORDER BY clause would need the expression SAL + NVL(COMM), using the column names without colons, not the item names with colons.

If the non-database item is populated by another query (ie, select loc into :nbt_ from dept where deptno = :emp.deptno) then an ORDER BY clause needs to be constructed that includes a sub-query, i.e. (SELECT LOC FROM DEPT D WHERE D.DEPTNO = E.DEPTNO) ++ mcs Received on Fri Mar 04 2005 - 09:03:08 CST