Re: [Fwd: Order by non-base table item in Forms 4.5]

In article <3815EADD.ED21CDBF_at_scpa.sri.com>, Scott Haas <haas_at_scpa.sri.com> writes
> I just want to clarify the situation I have and make sure that we are on the
>wave length - I have about 7 fields that are part of a base table. The 8th
>field is a non-base table item, however, it is calculated using fields from
>the base table fields. In addition to sorting (ordering by) the base table
>fields,
>
>I want to be able to sort on the non-base table item.

You are trying to produce a valid SQL statement in your form, as usual. The ORDER BY attribute of the block directly matches a standard ORDER BY clause, so it is quite valid to use a function here e.g. in a normal SQL query you might say

select town_name
  from all_towns
 order by my_package.my_ranking(town_name) ;

As long as the PRAGMA has been set-up on the package, the above would produce a query ordered using a function. In your form, you'd just put the 'my_package.my_ranking(town_name)' in the ORDER BY attribute of the block.

I do this all the time with intersection tables - you have two keys and on the post-query you get the description that goes with each of the keys. However, the resultant block should be ordered by the descriptions and not the key values - so I make functions to return the descriptions and use them in the ORDER BY.

Andy

-- 
Andy Hardy. PGP key available on request
===============================================================