Re: Order by non-base table fields

Hi Fred-
...
> The non-base table field is a look-up populated in post-query.
...

Well, unfortunately, that means it won't be populated until "Post" query. That means that the query has already been executed, and the order is set.

One option is to put whatever "lookup" function you are using to populate that column in a view (or, if you are using D2Kr2, then you can simply base the block on a query instead of putting the query in a view) and order by that. If you structure your primary and foreign keys correctly, you'll even be able to still update through that view ( except for the calculated column, of course ) without writing an "ON-" trigger.

Here's an example:

product: p_code is the PK
inventory: id is the PK, and p_code is an FK to product.p_code

SQL> select * from product;

   P_CODE NAME
--------- ----------

        1 BAT
        2 GLOVE
        3 BALL

SQL> select * from inventory;

   P_CODE QUANTITY ST ID
--------- --------- -- ---------

        1       150 DC         1
        1       180 MD         2
        2       145 MD         3
        2       145 VA         4
        3        90 DC         5

SQL> create view inventory_full as select inventory.*, product.name   2 from product, inventory
  3 where product.p_code = inventory.p_code;

SQL> select * from user_updatable_columns where table_name = 'INVENTORY_FULL'   2 /

OWNER      TABLE_NAME                     COLUMN_NAME                    UPD
---------- ------------------------------ ------------------------------ ---
TEST       INVENTORY_FULL                 P_CODE                         YES
TEST       INVENTORY_FULL                 QUANTITY                       YES
TEST       INVENTORY_FULL                 STATE                          YES
TEST       INVENTORY_FULL                 ID                             YES
TEST       INVENTORY_FULL                 NAME                           NO

Then base your form block on the view INVENTORY_FULL and list "name" in the "ORDER BY Clause" property of the block.

Good Luck, Dave

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Wed Jun 24 1998 - 16:28:11 CEST