• Leistungsspektrum
    Die passenden Lösungen für Ihre Anforderungen.
    Mehr Details unter der Rubrik: Leistungen
  • Professionelle Anwendungen
    Holen Sie mehr aus Ihren APEX Applikationen heraus.
  • Neueste Designs
    Nutzung von erweiterten Web 2.0 Möglichkeiten und Entwicklung von Corporate Designs
  • Individuelle Lösungen
    Entwicklung von Business-Anwendungen genau nach Ihren Wünschen.
  • Upgrade Lösungen
    Weiterentwicklung bestehender Anwendungen.
  • Schulungen und Vorträge
    Die passenden Schulungen genau für Sie zugeschnitten. Beispiel: Navigationsbeispiele
  • Plugins und Third Party Erweiterungen
    Erweitern Sie die Funktionalität ihrer Anwendungen mit einem modularen Entwicklungsansatz.

20 January, 2014

APEX Validation: Check overlapping time periods

There is a easy way to check if a row with overlapping time periods exists inside your table.

We assume that our table is called T_MACHINE with the columns m_id (PK), valid_from and valid_until 

All you need to do is to create the following APEX validation:
Validation type: NOT Exist

SQL:
select 1 from T_MACHINE
WHERE (:P6_ID IS NOT NULL AND M_ID != :P6_ID OR :P6_ID IS NULL)
AND   (VALID_FROM <= TO_DATE(:P6_UNTIL,'DD.MM.YYYY')) and (TO_DATE(:P6_FROM,'DD.MM.YYYY') <= VALID_UNTIL)

First time I needed this solution it took me half a day for development and testing.
This time same problem but the solution was not available anymore.

What to do? I asked the WWW! Yes, first shot was a goal:
http://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap

Solution was ready in less then 30 minutes.

2 comments:

Alex Nuijten said...

But will it work on a multi-user application? You might create an entry which is being created by someone else at the same time...

Unknown said...

Hi Tobias,

To check overlapping periods in Oracle Database you can use the function WM_OVERLAPS (http://docs.oracle.com/cd/B28359_01/appdev.111/b28396/long_vt.htm#g1014747)
With this function you get, in my opinion, easy to ready code.


WM_OVERLAPS(
WM_PERIOD(
VALID_FROM,
VALID_UNTIL
),
WM_PERIOD(
TO_DATE(:P6_FROM,'DD.MM.YYYY'),
TO_DATE(:P6_UNTIL,'DD.MM.YYYY')
)
) = 1


Best regards,

Carlos Pereira