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: 411
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 :
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