Forum Stats

  • 3,767,858 Users
  • 2,252,726 Discussions


LOV with conditions

Paul Jorstad
Paul Jorstad Member Posts: 69 Blue Ribbon
edited Sep 27, 2019 10:30AM in APEX Discussions

Let's say I have a modified EMP table:

create table emp( 

  empno  number(4,0), 

  ename varchar2(10), 

  job varchar2(9), 

  partnerno number(4,0),

  constraint pk_emp primary key (empno), 

  constraint fk_emp foreign key (partnerno ) references emp(empno)


This is exposed in an interactive grid. As you see there is a foreign key to the table itself for the column partnerno, which is, sort of, the partner to the employee. When working with the employees in the interactive grid I want a LOV to select the partner:

- The LOV should not show the employee on the same row. (An employee can't be a partner of himself).

- The LOV should not show employees that already are registered as a partner (one employee can have one and only one partner).

- The LOV should show the partner name in the interactive grid (when not in edit mode for example)

Suggestions to solve this?

<span class="hljs-operator"><span class="hljs-keyword"></span><br/></span>

Paul JorstadOra_Learner890


  • Rick-Pick
    Rick-Pick Member Posts: 79 Bronze Badge
    edited Sep 25, 2019 7:34AM

    You can try this:

    SELECT a.ename||' '||a.empno d, a.empno r

    FROM emp a

    WHERE a.empno != :EMPNO


      SELECT 1

      FROM emp b

      WHERE b.partnerno IS NOT NULL

      AND b.partnerno = a.empno



    SELECT c.ename||' '||c.empno, c.empno

    FROM emp c

    WHERE c.empno = (

      SELECT d.partnerno

      FROM emp d

      WHERE d.empno=:EMPNO)

    ORDER BY 1;

    You have to define the Cascading LOV Parent Column as EMPNO.

    The second part of the union only returns the partner of the current emp, if there is one. Without it you won't see the partner.

    Of course it will only work for saved records. If you need it to work dynamically across records without the user saving each time, you will need to use a JavaScript DA to add each partnerno to a hidden page item, separating them with ":", then use INSTR to eliminate them in the above query - clearing the page item when the records are saved. I think it can be done, but whether it's worth it is up to you!

    Paul JorstadPaul Jorstad
  • Paul Jorstad
    Paul Jorstad Member Posts: 69 Blue Ribbon
    edited Sep 25, 2019 8:09AM

    Thanks for replying! :EMPNO will be the EMPNO of the current row then? Anything I need to do to pass that bindvariable to the LOV query? Not sure if I can pick it up.. But will try this!

  • M Tajuddin
    M Tajuddin Member Posts: 505 Bronze Badge
    edited Sep 26, 2019 4:38AM


    You can try this, however, I haven't tested the code though

    SELECT ename, empno  FROM emp -- display the name of the empWHERE empno != :Px_EMP_NO -- matches the first conditionAND NOT EXISTS(SELECT 1 FROM emp WHERE partnerno != empno) -- exclude the emp who are assigned as partner
  • Ora_Learner890
    Ora_Learner890 Member Posts: 368
    edited Sep 26, 2019 7:08AM

    may be this help, didn't tested

    select b.ename partner_name, a.empno partner_no  from (      select empno from emp where empno <> :EMPNO -- first Select ALL except in current row      minus      select partnerno from emp  -- exclude ALL who are already assigned as Partner ) a, emp bwhere a.empno = b.empno
  • Paul Jorstad
    Paul Jorstad Member Posts: 69 Blue Ribbon
    edited Sep 27, 2019 3:37AM

    Thanks, it worked out! I'm not sure why we need to define it as a cascading LOV just to be able to use the values of the row? I tried to make the report refresh after a change in the partner column but didn't make it. If I created a dynamic action for the partner column, the value from the LOV was not saved, but report refreshed. Any idea how to set the value in the cell from LOV and then, save the interavtive grid? And the refresh it? IE: 1: Set the value from LOV, 2: Save the grid, 3: Refresh the grid. Would be nice :-)

  • Rick-Pick
    Rick-Pick Member Posts: 79 Bronze Badge
    edited Sep 27, 2019 10:30AM

    I'm not sure I would want to take control of the save away from the user, but I suppose you want to do it to avoid the issue I mentioned that the partner just added will not disappear from the LOV unless you do save. You can invoke a save on the IG with:


    But trying to do this within the grid itself as you're suggesting will I think cause a big headache. Much easier to just ask the users to save after each record if they change the partner field.

    Incidentally, I think you need the cascading LOV to make it dynamic (and also to ensure that the EMPNO is saved in session state).

    Paul JorstadPaul Jorstad