APEX

I have this IG

(plant, area, subarea, model columns are LOV. area and subarea are cascading LOV. And subarea column can be null)

the user adds a row, selects a plant, selects an area then subarea,

if the subarea is null then I want then i want to use the below sql query as the source of the lov.

SELECT R.MODEL_NAME, R.RELATIONSHIP_ID
FROM TAG_CLASS_EQUIP_CLASS_RELATIONSHIP R, model_hierarchy MH, AREA A
WHERE R.RELATIONSHIP_ID = MH.child_model_id
AND MH.parent_model_id = A.MODEL_ID
AND A.AREA_id = :AREA_ID
AND MODEL_TYPE_ID = 6;

if the subarea is not null then I want then i want to use the below sql query as the source of the lov.

SELECT R.MODEL_NAME, R.RELATIONSHIP_ID
FROM TAG_CLASS_EQUIP_CLASS_RELATIONSHIP R, model_hierarchy MH, SUBAREA SA
WHERE R.RELATIONSHIP_ID = MH.child_model_id
AND MH.parent_model_id = SA.MODEL_ID
AND SA.SUBAREA_id = :SUBAREA_ID
AND MODEL_TYPE_ID = 6';

i tried using function returning sql

DECLARE
    V_QUERY VARCHAR2(4000);
BEGIN
    IF :SUBAREA_ID IS NULL THEN
        V_QUERY := 'SELECT R.MODEL_NAME, R.RELATIONSHIP_ID
                    FROM TAG_CLASS_EQUIP_CLASS_RELATIONSHIP R, model_hierarchy MH, AREA A
                    WHERE R.RELATIONSHIP_ID = MH.child_model_id
                    AND MH.parent_model_id = A.MODEL_ID
                    AND A.AREA_id = :AREA_ID
                    AND MODEL_TYPE_ID = 6';
    ELSE
        V_QUERY := 'SELECT R.MODEL_NAME, R.RELATIONSHIP_ID
                    FROM TAG_CLASS_EQUIP_CLASS_RELATIONSHIP R, model_hierarchy MH, SUBAREA SA
                    WHERE R.RELATIONSHIP_ID = MH.child_model_id
                    AND MH.parent_model_id = SA.MODEL_ID
                    AND SA.SUBAREA_id = :SUBAREA_ID
                    AND MODEL_TYPE_ID = 6';
    END IF;
    RETURN V_QUERY;
END;

PLS Help with this