Mapping partial column values using Toplink

Question

Suppose I have a list of IDs as follows: EmployeeID ------- ABCD AECD ABDF ACDF ACDE I have a need to read the distinct values from a list of codes, while selecting only the first two characters of the column. In other words, its similar to using the following query:

SELECT DISTINCT LEFT (EmployeeID,2) FROM TABLE1

My question is how do I map such a field in TOPLINK.

Note:I have created a class for the EmployeeID, but dont have an idea of mapping a partial field.

Answer 1

Ok... After looking at many workarounds, I seem to have a more suited solution.

I created an object for this particular scenario (the POJO has only the field for the holding the 2 Char ID, and its getter and setter methods).

During the mapping, I mapped the above field to the DB column in question (EmployeeID in the table described above).

Now I selected "Custom Queries" for the above object and entered the following query for "Read all" tab.

SELECT DISTINCT LEFT (EmployeeID,2) AS EmploeeID FROM TABLE1

All the read all operations on the object will now return the list of distinct first 2 characters of IDs.

Welcome anyone's opinion on this.