Oracle Apex replace sql query string with select list value

Question

I have a page in Apex application where there is a page item which is a list, P1_DEPT.

select dept_code d, dept_id r from departments;

DEPT_CODE   DEPT_ID
AB1             1
CA2             2
DF3             3

The IG Query is:

select emp_code,employee_name,project,manager from employees;

Here emp_code data is :

AB123456
CA221345
DF399059

So the first 3 characters are deptcode and rest is employee id.

So if i filter AB1, all employees starting with AB1 as emp_code should get filtered.

What condition would be best to apply in sql where clause?

Answer 1

something like this should work:

select e.emp_code,e.employee_name,e.project,e.manager 
 from employees e
      join departments d ON substr(e.emp_code,1,3) = d.deptcode
      WHERE d.deptno = :P1_DEPT

However, instead of solving problems like this you should consider normalising the data model. The employees table should have a deptno column with a foreign key to the departments table (in addition to the empcode column). Then the join would just be on the deptno column. It will save you lots of time later on in the project.