Creating Dynamic LOVs

Question

I have a form in oracle apex with more than seven items on it. they are

SUBJECT_ID,GRADE_ID,DOMAIN_ID, CATEGORY_ID, STANDARD_CODE, STANADARD_STATEMENT, LEARNING_TARGETS.

I want these items SUBJECT_ID,GRADE_ID,DOMAIN_ID, CATEGORY_ID, STANDARD_CODE type to be select list. additionally, I want to make LOVs for each of these items.

LOV for SUBJECT_ID: I am making this LOV using a table SUBJECTS having TWO columns. MY query is SELECT SUBJECT_ID, SUBJECT_NAME FROM SUBJECTS It's working fine.

LOV for GRADE_ID: I am making this LOV using a table GRADES having TWO columns. MY query is SELECT GRADE_ID, GRADE_NAME FROM GRADES It's working fine.

LOV for DOMAIN_ID: I am making this LOV using a table DOMAIN having TRHEE columns. MY query is SELECT DOMAIN_ID, DOMAIN_NAME FROM DOMAIN WHERE SUBJECT=:P48_SUBJECT_ID. It's working fine.

LOV for CATEGORY_ID: I am making this LOV using a table CATEGORIES having FOUR columns. MY query is SELECT CATEGORY_ID, CATEGORY_NAME FROM CATEGORIES WHERE DOMAIN=:P4.8_DOMAIN_ID It's working fine.

LOV for STANDARD_CODE: I am making this LOV using a table CURRICULUM having MORE THAN EIGHT columns. MY query is SELECT CURRICULUM_ID CI, STANDARD_CODE SC FROM CURRICULUM WHERE SUBJECT=:P48_SUBJECT_ID AND GRADE_ID=:P48_GRADE_ID AND DOMAIN_ID=:P48_DOMAIN_ID AND CATEGORY_ID=:P48_CATEGORY_ID. It's not working for me.

Kindly tell me how I can correct the 5th LOV. Thanks

Answer 1

I wouldn't say that any of LoV queries you posted return desired result and "work fine". Their format should be:

select display_value,  --> you see it on the screen
       return_value    --> you don't see it; it is stored into the table
from ...

Code you posted suggest just the opposite, e.g.

SELECT SUBJECT_ID,     --> are you REALLY displaying ID to users and
       SUBJECT_NAME    --> storing NAME into the table?
FROM SUBJECTS

---

As of your final LoV: just as MT0 commented, we have no idea what "not working" means. You posted a whole lot of more or less useless information (queries that "work"; what should we do with them?), but said nothing about problem you have.

Therefore, I'll guess: you forgot to include

P48_SUBJECT_ID, P48_GRADE_ID, P48_DOMAIN_ID, P48_CATEGORY_ID

into the Parent Item(s) property within the "Cascading List of Values" section, e.g.

Note that query you posted presumes that all page items have a value; if any of these is NULL, query won't return anything so that would be my second guess:

SELECT curriculum_id ci, standard_code sc
  FROM curriculum
 WHERE     (   subject = :P48_SUBJECT_ID
            OR :P48_SUBJECT_ID IS NULL)
       AND (   grade_id = :P48_GRADE_ID
            OR :P48_GRADE_ID IS NULL)
       AND (   domain_id = :P48_DOMAIN_ID
            OR :P48_DOMAIN_ID IS NULL)
       AND (   category_id = :P48_CATEGORY_ID
            OR :P48_CATEGORY_ID IS NULL)

In that case, switch the "Parent required" property OFF.