custom query - how can we fetch the set of records based on BDM type attribute in custom query ?


i have two business objects as follows

employee - name String
- eType employeeType

employeeType - name String.

custom query :- SELECT e
FROM employee e
WHERE e.eType.persistenceId = :id
ORDER BY e.persistenceId ASC

the above query is not returning any result, i am using version 7.5.2

2 answers

This one is the BEST answer!

Actually the issue is I think in your query syntax. Actually as Employee and EmployeeType are two different tables you need to have a join (AFAIK, I'm not a JPQL expert).

Here is a version that I think should do what you want:

FROM Employee e 
JOIN e.eType type
WHERE type.persistenceId = :inputId
ORDER BY e.persistenceId ASC


Why are you using a separate table "employeeType" just to hold a list of strings? I'd make the "eType" field of "employee" a string, and then it's simple.


Submitted by peershariff on Wed, 07/12/2017 - 10:00

Thanks for replying...

I am using a separate table because it will be helpful while preparing a report based on employeeType. instead of user entering the text he will select it from the dropdown, other wise the user may enter text with spelling mistakes.

Submitted by chris.lowth on Fri, 07/14/2017 - 16:24

Ok - but you can populate a dropdown of fixed options in a form without having to hold the options in a table. If you want them to be configurable there are plenty of ways of doing that without adding complexity to the DB definition.