ORA-00907右右括号缺失

Question

在处理下面的查询时，我得到了"缺少右括号"的错误。

SELECT DESCRIPTION, TOTAL_ADMISSION, EMERGENCY, NORMAL      
FROM TABLE (ORDERENTRY.PKG_S04REP00031.ADMISSION_SUMMARY
(P_START_DATE => to_date('01/08/2021', 'dd/mm/yyyy'),
P_END_DATE => to_date('01/09/2021', 'dd/mm/yyyy'),
P_ADMISSION_TYPE    => 'N',
P_ORDER_LOCATION_ID in (SELECT DESCRIPTION FROM DEFINITIOS.DEPARTMENT),
P_LOCATION_ID => 'K01'));

Answer 1

在Oracle 12c中，您可以使用CROSS APPLY:

SELECT t.DESCRIPTION,
t.TOTAL_ADMISSION,
t.EMERGENCY,
t.NORMAL      
FROM   DEFINITIOS.DEPARTMENT d
CROSS APPLY TABLE(
ORDERENTRY.PKG_S04REP00031.ADMISSION_SUMMARY(
P_START_DATE        => DATE '2021-08-01',
P_END_DATE          => DATE '2021-09-01',
P_ADMISSION_TYPE    => 'N',
P_ORDER_LOCATION_ID => d.description,
P_LOCATION_ID       => 'K01'
)
) t;

或CROSS JOIN LATERAL:

SELECT t.DESCRIPTION,
t.TOTAL_ADMISSION,
t.EMERGENCY,
t.NORMAL      
FROM   DEFINITIOS.DEPARTMENT d
CROSS JOIN LATERAL (
SELECT *
FROM   TABLE(
ORDERENTRY.PKG_S04REP00031.ADMISSION_SUMMARY(
P_START_DATE        => DATE '2021-08-01',
P_END_DATE          => DATE '2021-09-01',
P_ADMISSION_TYPE    => 'N',
P_ORDER_LOCATION_ID => d.description,
P_LOCATION_ID       => 'K01'
)
)
) t;

db<此处小提琴>

Answer 2

你不能用这样的参数来调用过程/函数P_ORDER_LOCATION_ID in (SELECT DESCRIPTION FROM DEFINITIOS.DEPARTMENT)它应该是P_ORDER_LOCATION_ID =祝辞

但是仍然在过程/函数参数中调用select ?