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.
 
 

Background
 
 

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_PRODUCT.NAME is a unique key of HDD_PRODUCTS.

HDD_PRODUCT.REFERENCE is a unique 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:

• Users may want to change it, and if they insist on it we get into the old "Cascade Update Foreign Keys" nightmare.
• We may use the product table as a lookup in another module, but ordered by product reference this time.

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:

• use a dummy where clause (1=1) because we did not need anything real in this module;
• use an order by clause into an area that most people would consider reserved to where clauses;
• use a database function to order records.

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);

end;

Header of Package hdd_order.

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';

begin

open get_product_name;

fetch get_product_name into return_value;

close get_product_name;

return return_value;

end;

end;

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.