oracle Apex 值列表定义

Apex List Of Values definition

提问by Sergey

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

有没有办法查询 :P5_NS_ID 指向的表？执行此查询时出错。

回答by Richard Pascual

Building an APEX List of Values with Cascading References

使用级联引用构建 APEX 值列表

@Frank Schmittwas close in his solution with his query suggestion:

@Frank Schmitt在他的查询建议中非常接近他的解决方案：

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:

然而，要克服您遇到的错误，需要提醒您尝试创建的 Oracle APEX 元素类型背后的规则，LIST OF VALUES QUERY. 以下是 Apex 开发人员区域中发布的基于查询的 LOV 示例的屏幕截图：

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.

请注意，查询的输出中需要有两列。位置 1 中的值是DISPLAY VALUE（在使用它的选择对话框中显示的内容。）和RETURN VALUE（实际作为输入值返回到显示查询选项的页面项目的内容。

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

设置示例模式，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:

此示例将使用三个 LOV 查询：

1. The first LOV Query is in page item: P3_FIRST_CHOICEis independentof any value. It offers the user a choice of DEPARTMENTvalues.

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

2. The second LOV Query is in page item: P3_SECOND_CHOICEand 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_CHOICEthe first CASCADING PARAMETERvalue, 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_CHOICEand relies on the input of the selections for: P3_FIRST_CHOICEand P3_SECOND_CHOICE.

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

   P3_THIRD_CHOICEhas a CASCADING PARAMETERdependency and offers a selection of employee names who have records that match the selected DEPARTMENTand JOBvalues from the first two form selection items.

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

1. 第一LOV查询页面项目：P3_FIRST_CHOICE是独立于任何价值的。它为用户提供了DEPARTMENT值的选择。

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

2. 第二个 LOV 查询位于页面 item: 中P3_SECOND_CHOICE，依赖于 的选择输入P3_FIRST_CHOICE。

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

   这是P3_SECOND_CHOICE第一个CASCADING PARAMETER值，因此级联 LOV 父项是： P3_FIRST_CHOICE。对于给定的部门，用户可以选择特定的JOB.

   请注意，即使返回值与显示相同，LOV 查询输出中也需要两列。

3. 第三个 LOV 查询位于页面 item: 中，P3_THIRD_CHOICE并依赖于以下选项的输入： P3_FIRST_CHOICE和P3_SECOND_CHOICE。

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

   P3_THIRD_CHOICE具有CASCADING PARAMETER依赖关系并提供员工姓名的选择，这些员工姓名的记录与前两个表单选择项中的选定项DEPARTMENT和JOB值相匹配。

4. 有一个可选的第四个查询，用于填充EMPLOYEE从第三个 LOV 选择列表中选择的报告。

Your APEX form design may look something like this:

您的 APEX 表单设计可能如下所示：

How to Get New Parameters From Cascading Form Input Items

如何从级联表单输入项中获取新参数

This is the example query form:

这是示例查询表单：

Entry Selection for the FIRST CHOICE (Department):

FIRST CHOICE（系）的条目选择：

Entry Selection for the SECOND CHOICE (Job):

第二选择（工作）的入学选择：

Entry Selection for the THIRD CHOICE (Employee):

第三个选择（员工）的条目选择：

Final Output: Individual Query by Selected Employee

最终输出：选定员工的个人查询

回答by Frank Schmitt

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

我不是 APEX 专家，但此查询看起来已损坏 - 您正在返回server_name从内部查询调用的单个列，并且您尝试引用外部查询中称为"name"和 的两列"type"。

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

如果您的表dns_servers确实包含名为"name"and 的列"id"，我建议从内部查询返回所有列：

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