Combo/Input LOV displaying non-reference key value

Posted by [email protected] on Oracle Blogs See other posts from Oracle Blogs or by [email protected]
Published on Thu, 15 Apr 2010 22:03:22 +0000 Indexed on 2010/04/15 22:34 UTC
Read the original article Hit count: 414

Filed under:
Its a very common use-case of LOV that we want to diplay a non key value in the LOV but store the key value in the DB. I had to do the same in a sample application I was building.
During implementation of this, I realized that there are multiple ways to achieve this.
I am going to describe each of these below.

Example : Lets take an example of our classic HR schema. I have 2 tables Employee and Department where Dno is the foreign key attribute in Employee that references Department table.
I want to create a LOV for Deparment such that the List always displays Dname instead of Dno. However when I update it, it it should update the reference key Dno.

To achieve this I had 3 alternative

1) Approach 1 :
Create a composite VO and add the attributes from Department into Employee using a join.
Refer the blog
http://andrejusb.blogspot.com/2009/11/defining-lov-on-reference-attribute-in.html
Positives :
1. Easy to implement and use.
2. We can use this attribute directly in queries defined on new attribute i.e If i have to display this inside query panel.

Negative : We have to create an additional Join on the VO.
Ex:
SELECT Employees.EMPLOYEE_ID,
       Employees.FIRST_NAME,
       Employees.LAST_NAME,
       Employees.EMAIL,
       Employees.PHONE_NUMBER,
      Department.Dno,
       Department.Dname
FROM EMPLOYEES Employees, Department Department
WHERE Employees.Dno = Department .Dno

2) Approach 2 :

© Oracle Blogs or respective owner