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
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.
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:
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.
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.
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
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.
package body hdd_order is
function sort_orders(p_id in integer) return varchar2 is
cursor get_product_name is
where id = p_id;
return_value varchar2(100) := ' Unknown';
fetch get_product_name into 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
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 firstname.lastname@example.org.
Take a look at my HomePage