Apex List Of Values definition

Question

Is there a way to query the table that :P5_NS_ID poits to? Get an error when doing this query.

Answer 1

Building an APEX List of Values with Cascading References

@Frank Schmitt was close in his solution with his query suggestion:

select "name" from (
  select * from dns_servers where id = :P5_NS_ID)
where "type" = :P5_REC_TYPE_ID

To get over the errors you encountered however requires a few reminders of the rules behind the type of Oracle APEX element you are trying to create, a LIST OF VALUES QUERY. Here's a screenshot of the Examples for query based LOVs posted in the Apex developer area:

Note that there needs to be TWO columns in the output of your query. The value in position 1 is the DISPLAY VALUE (what is shown in the selection dialogue that uses it.) And RETURN VALUE (what is actually returned as the input value to the page item that shows the options from the query.

You may also be having some difficulty with implementing the logic of cascading input parameters. Below is an example of how to figure it out.

Setting up the Example Schema, HR-EMP

Here are the table structures used in my example:

DEPT

CREATE TABLE  "DEPT" 
   (    "DEPTNO" NUMBER(2,0), 
        "DNAME" VARCHAR2(14), 
        "LOC" VARCHAR2(13), 
     PRIMARY KEY ("DEPTNO") ENABLE
   )
/

EMP

CREATE TABLE  "EMP" 
   (    "EMPNO" NUMBER(4,0) NOT NULL ENABLE, 
        "ENAME" VARCHAR2(10), 
        "JOB" VARCHAR2(9), 
        "MGR" NUMBER(4,0), 
        "HIREDATE" DATE, 
        "SAL" NUMBER(7,2), 
        "COMM" NUMBER(7,2), 
        "DEPTNO" NUMBER(2,0), 
         PRIMARY KEY ("EMPNO") ENABLE
   )
/

ALTER TABLE  "EMP" ADD FOREIGN KEY ("MGR")
    REFERENCES  "EMP" ("EMPNO") ENABLE
/
ALTER TABLE  "EMP" ADD FOREIGN KEY ("DEPTNO")
    REFERENCES  "DEPT" ("DEPTNO") ENABLE
/

This example will use three LOV queries:

1. The first LOV Query is in page item: P3_FIRST_CHOICE is independent of any value. It offers the user a choice of DEPARTMENT values.

   SELECT DNAME || ', ' || LOC as d, DEPTNO as r
     FROM DEPT     
   

2. The second LOV Query is in page item: P3_SECOND_CHOICE and relies on the input of the selection for P3_FIRST_CHOICE.

   SELECT distinct JOB d, JOB r
     FROM emp
    WHERE emp.deptno = :P3_FIRST_CHOICE
   

   This makes P3_SECOND_CHOICE the first CASCADING PARAMETER value, so the cascading LOV Parent Item is: P3_FIRST_CHOICE. For a given department the user may select a specific JOB.

   Notice that even if the return value is the same as the display, TWO columns are required in an LOV query output.

3. The third LOV Query is in page item: P3_THIRD_CHOICE and relies on the input of the selections for: P3_FIRST_CHOICE and P3_SECOND_CHOICE.

   SELECT ENAME d, EMPNO r
     FROM emp
    WHERE DEPTNO = :P3_FIRST_CHOICE
      AND JOB = :P3_SECOND_CHOICE
   

   P3_THIRD_CHOICE has a CASCADING PARAMETER dependency and offers a selection of employee names who have records that match the selected DEPARTMENT and JOB values from the first two form selection items.

4. There is an optional fourth query which populates a report on the EMPLOYEE that is selected from the third LOV selection list.

Your APEX form design may look something like this:

How to Get New Parameters From Cascading Form Input Items

This is the example query form:

Entry Selection for the FIRST CHOICE (Department):

Entry Selection for the SECOND CHOICE (Job):

Entry Selection for the THIRD CHOICE (Employee):

Final Output: Individual Query by Selected Employee

Answer 2

I'm not an APEX expert, but this query looks broken - you're returning a single column called server_name from your inner query, and you're trying to reference two columns called "name" and "type" in your outer query.

If your table dns_servers indeed contains columns called "name" and "id", I'd suggest returning all columns from the inner query:

  select "name" from (
    select * from dns_servers where id = :P5_NS_ID)
  where "type" = :P5_REC_TYPE_ID