Re: Order by non-base table fields

Hi Fred,

As long as you are using either Forms 5 or the very latest patch release of Forms 4.5, then I would suggest that Valery A Sorokins answer is the right one in that the function returns the value that would have been assigned by the post-query trigger into the order-by clause.

The down side of this is that you still have to repeat the function in the post-query trigger if the value is to be displayed in the form.

Another potential downside is that if you have a very dynamic multi-user environment and if the calculation is based on dynamic data and if the users either can execute large queries or the query time is relatively slow for other reasons such as network traffic, then it is possible for the function in the order by and the function in the post-query trigger to both retrieve different values due to one being performed during query parse and execution and the other being performed during each row fetch.

Hope this is helpful,

Rod Corderey

Lane Associates
Lane_Associates_at_Compuserve.com
http://www.Lane-Associates.com

Fred Joughin wrote:
>
> Sorry, I wasn't very explicit about this. The non-base table field is a look-up
> populated in post-query. The base table is based on the Products table and contains
> a supplier_code which is a foreign key to the Suppliers table. The Suppliers table
> includes a field called 'name'. I want to list the products, ordered by supplier
> name.
>
> DanHW wrote:
>
> > >I have a simple block with some none base-table fields. I want the
> > >records in the block to be ordered by one of the non-base table fields.
> > >How can I do it ?
> > >
> > >Fred Jougin
> >
> > How is the non-base column getting filled? it sounds like every row returned
> > would have the same value, so any kind of order by would have no effect.
> >
> > That said, you can use form parameters and globals to generate the order by
> > clause at runtime, and use the SET_BLOCK_PROPERTY function to set the ORDER_BY
> > for the block. Set it in the PRE-QUERY trigger.
> >
> > Dan Hekimian-Williams
Received on Thu Jun 25 1998 - 18:01:28 CEST