Joining tables as Outer Join & Create POP Up LOV from View in Oracle Apex

 We found Tables of Following along with Data.

Created the Tables :

CREATE TABLE "COUNTRY" 
   ( "COUNTRY_ID" VARCHAR2(3), 
"COUNTRY_NAME" VARCHAR2(100), 
CONSTRAINT "PK_COUNTRY" PRIMARY KEY ("COUNTRY_ID") USING INDEX  ENABLE
   ) ;
  CREATE TABLE "DIVISION" 
   ( "DIV_ID" VARCHAR2(10), 
"DIV_NAME" VARCHAR2(100), 
"DIV_NAME_NL" VARCHAR2(100), 
"DIV_LAT" VARCHAR2(100), 
"DIV_LON" VARCHAR2(100), 
"DIV_WEB" VARCHAR2(100), 
"DIV_CODE" VARCHAR2(10), 
"COUNTRY" VARCHAR2(10), 
CONSTRAINT "PK_DIVISION1" PRIMARY KEY ("DIV_ID")  USING INDEX  ENABLE
   ) ;
  ALTER TABLE "DIVISION" ADD CONSTRAINT "FK_DIVISION1" FOREIGN KEY ("COUNTRY") REFERENCES "COUNTRY" ("COUNTRY_ID") ENABLE;

CREATE TABLE "DISTRICT" 

   ( "DIST_ID" VARCHAR2(10), 

"DIST_NAME" VARCHAR2(100), 

"DIST_NAME_NL" VARCHAR2(100), 

"DIST_ISO_CODE" VARCHAR2(30), 

"DIST_LAT" VARCHAR2(100), 

"DIST_LON" VARCHAR2(100), 

"DIST_WEB" VARCHAR2(100), 

"DIV_CODE" VARCHAR2(10), 

CONSTRAINT "PK_DISTRICT1" PRIMARY KEY ("DIST_ID")

  USING INDEX  ENABLE

   ) ;

  ALTER TABLE "DISTRICT" ADD CONSTRAINT "FK_DISTRICT1" FOREIGN KEY ("DIV_CODE")

  REFERENCES "DIVISION" ("DIV_ID") ENABLE;

CREATE TABLE "UPZ" 

   ( "UPZ_ID" VARCHAR2(10), 

"UPZ_NAME" VARCHAR2(100), 

"UPZ_NAME_NL" VARCHAR2(100), 

"UPZ_LAT" VARCHAR2(100), 

"UPZ_LON" VARCHAR2(100), 

"UPZ_WEB" VARCHAR2(100), 

"ADM_TYPE" VARCHAR2(30), 

"DIST_CODE" VARCHAR2(10), 

CONSTRAINT "PK_UPZ" PRIMARY KEY ("UPZ_ID")

  USING INDEX  ENABLE

   ) ;

  ALTER TABLE "UPZ" ADD CONSTRAINT "FK_UPZ" FOREIGN KEY ("DIST_CODE")

  REFERENCES "DISTRICT" ("DIST_ID") ENABLE;

Upload the Data :

Create View :

  CREATE OR REPLACE FORCE EDITIONABLE VIEW "VIEW_LOCATION" ("UPZ_ID", "UPZ_NAME", "DIST_CODE", "DIST_NAME", "DIV_CODE", "DIV_NAME") AS 

  SELECT X.UPZ_ID,X.UPZ_NAME,X.DIST_CODE,X.DIST_NAME,X.DIV_CODE,

       Y.DIV_NAME

FROM

(SELECT A.UPZ_ID,A.UPZ_NAME,A.DIST_CODE,B.DIST_ID,B.DIST_NAME,B.DIV_CODE

FROM

(SELECT UPZ_ID,UPZ_NAME,DIST_CODE FROM UPZ) A,

(SELECT DIST_ID,DIST_NAME,DIV_CODE FROM DISTRICT) B

WHERE A.DIST_CODE = B.DIST_ID

) X,

(SELECT DIV_ID,DIV_NAME,COUNTRY FROM DIVISION) Y

WHERE X.DIV_CODE = Y.DIV_ID

WITH READ ONLY;

OR :

CREATE OR REPLACE VIEW LOCATION AS

SELECT A.UPZ_ID,A.UPZ_NAME,A.DIST_CODE,B.DIST_NAME,B.DIV_CODE,

       C.DIV_ID,C.DIV_NAME,C.COUNTRY

FROM

(SELECT UPZ_ID,UPZ_NAME,DIST_CODE FROM UPZ) A,

(SELECT DIST_ID,DIST_NAME,DIV_CODE FROM DISTRICT) B,

(SELECT DIV_ID,DIV_NAME,COUNTRY FROM DIVISION) C

WHERE A.DIST_CODE = B.DIST_ID

AND   B.DIV_CODE = C.DIV_ID;

NOW CREATING POPUP LOVs :

Resource Link :

https://drive.google.com/file/d/140oPfN2X--FRKu1IJ00Qp__zEsRPhjiF/view?usp=sharing