Select List aktualisieren - Drei Select Listen nach Abhängigkeiten aktualisieren

wie können drei verschiedene "Select Listen" (DropDown) nach ihrer Abhängigkeit aktualisiert werden?

Hierfür müssen die Select Listen miteinander gekoppelt werden.Im folgenden wird versucht die Vorgehensweise in Detail zu erläutern.

Exemplifikation

Zunächst werden drei verschiedene Item als "Select List" angelegt:
1. P140_YEAR
2. P140_MONTH
3. P140_DAY

Zu 1: Das erste Item enthält einen SQL-Statement.

Zu 2: Das zweite Item enthält ebenfalls einen SQL-Statement, jedoch gibt es hierbei eine Besonderheit. Denn der SQL-Statement enthält die "P140_YEAR"-Variable. Das heißt, sofern sich jedesmal das erste "Select List"-Item ändert, wird das zweite "Select List"-Item zurück gesetzt, da sich der Inhalt aktualisiert.

Zu 3: Hierfür erfolgt die gleiche Implementierungs-Vorgehensweise, also der SQL-Statement enthält die "P140_MONTH"-Variable.

-------
WICHTIG
-------

Damit die Zwei Select Listen sich automatisch aktualisieren, muss folgendes eingestellt werden:

1. Eine "Select List" auswählen z.B. P140_MONTH
2. Auf dem Reiter "List of Values" gehen
3. Beim "Cascading LOV Parent Item(s)" den "P140_YEAR"-Item hinterlegen.
4. Somit weis das "P140_MONTH"-Item, sofern sich das "P140_YEAR"-Item ändert, muss der "P140_MONTH"-Item aktualisiert werden.
5. Fertig!

Im Folgenden werden die drei SQL-Statement der drei "Select Listen" dargestellt.

Download

Quellcode

      
          Für diesen Anwendungsfall wurde das folgende Quellcode verwendet.

 
----------------------------
-- Year
----------------------------
with Month as
(
 select TRUNC(SYSDATE - (360*5) , 'Year') as min_start_date
      , max(sysdate) as max_end_date
   from dual
)
select to_char(my_month, 'dd.mm.YYYY') as d
     , my_month as r
 from (
       select add_months(min_start_date, level * 12) as my_month
         from Month
       connect by level * 12 < months_between(max_end_date, min_start_date) + 2
      )
order by my_month desc

----------------------------
-- Month
----------------------------
with Month as
(
 select min(:P140_YEAR) as min_start_date
      , max(to_date(:P140_YEAR) + 330)  as max_end_date
   from dual
   order by min_start_date desc
)
select to_char(my_month, 'Month YYYY') as d
     , my_month as r
 from (
       select add_months(min_start_date, level - 1) as my_month
         from Month
       connect by level < months_between(max_end_date, min_start_date) + 2
      )
order by my_month ASC

----------------------------
-- Day
----------------------------
SELECT to_char(to_date(:P140_MONTH, 'DD.MM.YYYY') + LEVEL - 1, 'DD.MM.YYYY') as d
    ,         to_date(:P140_MONTH, 'DD.MM.YYYY') + LEVEL - 1                as r
FROM DUAL
CONNECT BY LEVEL <= to_date(:P140_MONTH, 'DD.MM.YYYY') + 30- to_date(:P140_MONTH, 'DD.MM.YYYY');
​x
 
----------------------------
-- Year
----------------------------
with Month as
(
 select TRUNC(SYSDATE - (360*5) , 'Year') as min_start_date
      , max(sysdate) as max_end_date
   from dual
)
select to_char(my_month, 'dd.mm.YYYY') as d
     , my_month as r
 from (
       select add_months(min_start_date, level * 12) as my_month
         from Month
       connect by level * 12 < months_between(max_end_date, min_start_date) + 2
      )
order by my_month desc
​
----------------------------
-- Month
----------------------------
with Month as
(
 select min(:P140_YEAR) as min_start_date
      , max(to_date(:P140_YEAR) + 330)  as max_end_date
   from dual
   order by min_start_date desc
)
select to_char(my_month, 'Month YYYY') as d
     , my_month as r
 from (
       select add_months(min_start_date, level - 1) as my_month
         from Month
       connect by level < months_between(max_end_date, min_start_date) + 2
      )
order by my_month ASC
​
----------------------------
-- Day
----------------------------
SELECT to_char(to_date(:P140_MONTH, 'DD.MM.YYYY') + LEVEL - 1, 'DD.MM.YYYY') as d
    ,         to_date(:P140_MONTH, 'DD.MM.YYYY') + LEVEL - 1                as r
FROM DUAL
CONNECT BY LEVEL <= to_date(:P140_MONTH, 'DD.MM.YYYY') + 30- to_date(:P140_MONTH, 'DD.MM.YYYY');

----------------------------
-- Year
----------------------------
with Month as
(
 select TRUNC(SYSDATE - (360*5) , 'Year') as min_start_date
      , max(sysdate) as max_end_date
   from dual
)
select to_char(my_month, 'dd.mm.YYYY') as d
     , my_month as r
 from (
       select add_months(min_start_date, level * 12) as my_month
         from Month
       connect by level * 12 < months_between(max_end_date, min_start_date) + 2
      )
order by my_month desc

----------------------------
-- Month
----------------------------
with Month as
(
 select min(:P140_YEAR) as min_start_date
      , max(to_date(:P140_YEAR) + 330)  as max_end_date
   from dual
   order by min_start_date desc
)
select to_char(my_month, 'Month YYYY') as d
     , my_month as r
 from (
       select add_months(min_start_date, level - 1) as my_month
         from Month
       connect by level < months_between(max_end_date, min_start_date) + 2
      )
order by my_month ASC

----------------------------
-- Day
----------------------------
SELECT to_char(to_date(:P140_MONTH, 'DD.MM.YYYY') + LEVEL - 1, 'DD.MM.YYYY') as d
    ,         to_date(:P140_MONTH, 'DD.MM.YYYY') + LEVEL - 1                as r
FROM DUAL
CONNECT BY LEVEL <= to_date(:P140_MONTH, 'DD.MM.YYYY') + 30- to_date(:P140_MONTH, 'DD.MM.YYYY');

Demo Anwendung & Blog

https://apex.oracle.com/pls/apex/f?p=74153:500

https://apexnote.blogspot.com/2018/09/select-list-aktualisieren-drei-select.html

04-SEP-2018

17-NOV-2018

APEX Note