I would like to select employee name from employee table and then the employee number should be appeared based on the employee name. When I choose one name it worked perfectly and the employee number that related to the employee is appeared as shown in the following image: works fine with one value

But the problem is, when I try to choose multiple names the multiple employee numbers did not appear as shown in the following image:

didn't work for multiple values

for the SQL query and cascading LOV I did the following:

SQL query and cascading LOV for employee name shuttle SQL query and cascading LOV for employee number shuttle

CodePudding user response：

Please don't use screenshots for code like sql queries. No one likes to copy test by typing it word by word. We all want to copy and paste but can't be done for screenshots.

The issue you have is that the select for your 2nd shuttle has a where clause that assumes a single value

  LIKE :ENAME

A shuttle stores the selected values in a colon separated list (eg KING:JAMES) but that won't work for your query. That explains why you see no results.

Solution is to assume that the bind variable comes in as a colon separated list and split it. APEX_STRING.SPLIT does a great job at that. Change the where clause to:

  WHERE ename IN (SELECT column_value FROM table(apex_string.split(:ENAME,':'))

In the settings for the EMPNO page item, only set "Parent Item" to ENAME, no need to submit any items. Only ENAME is used in the query and that is submitted by default.

CodePudding user response：

This is how I see it:

• shuttle item is just another List of Values

• in Apex, their queries return two values:

  • display value
  • return value

• usually, we write it as

  select ename as display_value,
         empno as return_value
  from emp
  

• it means that the shuttle item displays employee name (which is what you have), but ...

• ... you don't have to create a new shuttle item which will contain employee numbers because previous shuttle item already contains them as "return" values, colon-separated

Therefore, when you're going to do something with employee numbers (empno in my example), you just have to split those values into rows, e.g.

SQL> with
  2  temp (p1_shuttle) as
  3    -- this represents the P1_SHUTTLE item that contains 2 return values: 7369 and 7499
  4    (select '7369:7499' from dual),
  5  split_to_rows as
  6    -- this is how you'd split shuttle item into rows
  7    (select regexp_substr(p1_shuttle, '[^:] ', 1, level) one_value
  8     from temp
  9     connect by level <= regexp_count(p1_shuttle, ':')   1
 10    )
 11  -- finally, use those values
 12  select e.ename, e.job
 13  from emp e join split_to_rows s on s.one_value = e.empno;

ENAME      JOB
---------- ---------
SMITH      CLERK
ALLEN      SALESMAN

SQL>

Shortly, I believe you don't need the 2nd shuttle item at all.

CodePudding user response：

you might also try that in your query:

where regexp_like(:ENAME,ename)

(untested, worked for me in another but similar scenario)