Hello,
I am on Apex 4.2
Trying to join 2 tables using Apex collection:
DECLARE
vCOUNT NUMBER;
vSYSID NUMBER;
BEGIN
IF APEX_COLLECTION.COLLECTION_EXISTS('EMPS') THEN
APEX_COLLECTION.DELETE_COLLECTION('EMPS');
END IF;
APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY_B('EMPS', 'SELECT NVL(EMP_ID,0), NVL(DEPT_ID,0), COUNT(*) FROM EMPS, DEPTS Where EMP_DEPT_ID = DEPT_ID GROUP BY EMP_ID, DEPT_ID ORDER BY EMP_ID');
:P5_COUNT_ID := 1;
SELECT APEX_COLLECTION.COLLECTION_MEMBER_COUNT('EMPS') INTO vCOUNT FROM DUAL;
:P5_EMP_ID_COUNT := vCOUNT;
END;
If I query a single table using this collection, it works fine:
SELECT EMP_ID, FIRST_NAME ||' '|| LAST_NAME as "Employee Name", DATE_OF_BIRTH....
FROM EMPS
WHERE EMP_ID = (SELECT TO_NUMBER(C001) FROM APEX_COLLECTIONS WHERE COLLECTION_NAME = 'EMPS' AND SEQ_ID = TO_NUMBER(:P5_COUNT_ID))
ORDER BY EMP_ID;
However if I try to join another table I get "No Data Found" error:
SELECT EMP_ID, FIRST_NAME ||' '|| LAST_NAME as "Employee Name", DATE_OF_BIRTH, DEPT_NAME
FROM EMPS, DEPTS
WHERE EMP_ID = (SELECT TO_NUMBER(C001) FROM APEX_COLLECTIONS WHERE COLLECTION_NAME = 'EMPS' AND SEQ_ID = TO_NUMBER(:P5_COUNT_ID))
AND DEPT_ID = (SELECT TO_NUMBER(C002) FROM APEX_COLLECTIONS WHERE COLLECTION_NAME = 'EMPS' AND SEQ_ID = TO_NUMBER(:P5_COUNT_ID))
ORDER BY EMP_ID;
I am trying to do the same as described in this old threat: https://community.oracle.com/thread/842718
The only difference I need is to get data from 2 tables.
What am I going wrong?
Thank you.
Added some clarification to the code