Ordering by lookup DCU using Designer 2000.

By Hervé Deschamps, Sofstar Compu. Consultants Ltd.

In this article, we will take you step by step through a simple method to order records by ascending value of a column not present in the table! We will be using an example that everybody can relate to: customers, orders, and products. In a master detail form showing the history of products ordered by the customers, we want the products to be displayed in alphabetical order. We will achieve this 100% generated from Designer 2000. In case you find this introduction a bit on the cryptic side, Figure A will make it clearer.

Figure A

The final result, 100% generated.

We have sorted the products by alphabetical order despite the fact that the product name does not exist in the order table.



If you happen to think that this is really easy, here is why it is not. The product name is not in the order table. This is best explained using Designer 2000 module data diagram, shown in Figure B.

Figure B

Module Data Diagram. Note location of product name.

HDD_PRODUCT.ID is the primary key of HDD_PRODUCTS.



HDD_ORDERS.HPROD_ID is the foreign key pointing to HDD_PRODUCTS.

We have could have used the product name as the primary key of the product. But this would be a bad database design:

We belong to the school of thought that stay away from using primary keys that carry meaning unless the users guarantee that they will never ask us to make these things updateable.

So, we use IDs virtually everywhere in the database. IDs are populated by a database sequence. Most of the time, users do not see them at all - except when they define their own reports not based on views.

User meaningful IDs are defined as unique keys.

The method

In order to achieve this alphabetical order, we need not denormalise, define dangerous primary keys or use updateable views. A simple database function will do.

Step 1: Define the module basics

In Designer 2000, define the complete module, table usages and column usages. It is not the purpose of this article to explain how to do this.

Step 2: Use Where/Validation Clause

This is the essence of the technique. Figure C shows the where/validation clause of the Detailed Table Usage HDD_ORDERS.

Figure C

Where/Validation clause of HDD_ORDERS

This clause uses several tricks:


Step 3: Define the database function

Of course, you cannot generate this module quite yet. You need to code this database function that we used in Figure C.

You may define this function in a number of ways. The most efficient method for us is to create the database function in Developer 2000 (in Forms Designer -> Database Objects), save it to the database from there and then reverse-engineer in Designer 2000 repository.

We have chosen to group all our order-by functions into a package. You will find the package header in Listing A and the package body in Listing B.

Listing A

package hdd_order is

function sort_orders (p_id in integer) return varchar2;

pragma restrict_references (sort_orders, WNDS, WNPS);


Header of Package hdd_order.


Note: If you do not use the pragma statement, you will get the following error message: "ORA-06571: Function SORT_ORDERS does not guarantee not to update database". The pragma sets the purity level of the function. WNDS means "Write No Database State". WNPS means "Write No Package State". In other terms, this guarantees that function sort_orders is read-only. For more information, we recommend Steve Feuerstein’s book: "Oracle PL/SQL Programming".
Listing B

package body hdd_order is

function sort_orders(p_id in integer) return varchar2 is

cursor get_product_name is

select name

from hdd_products

where id = p_id;

return_value varchar2(100) := ' Unknown';


open get_product_name;

fetch get_product_name into return_value;

close get_product_name;

return return_value;



Body of Package hdd_order.


Function "sort_orders" simply returns the product name corresponding to the product id passed as a parameter. If no product is found, the string ‘ Unknown’ is returned to avoid any error.

Step 4: Generate away!


Last step, generate you module, run it and enjoy the power of Designer 2000 generators.

Last Word


Although this article makes the technique appear easy, it has taken a few people to put all the bricks together. The author would like to thank all of them, particularly John C. Lennon, Scott Lawrence and Roel Hartman for their contributions.

Hervé Deschamps is a senior Consultant for Softstar Compu. Consultants. He has had four years of experience with Oracle Development Environment (Designer 2000, Developer 2000 and their previous versions). He specializes in tending towards "100% Generation". You can reach him by E-mail at herve@iherve.com.

 Take a look at my HomePage