A dynamic List of Values (LOV) in Oracle APEX is used to fetch data that will be in a constant state of change.  For this blog, we will be examining a real-world example that uses a dynamic LOV with a subquery to help book clients to rooms.

The Situation

A few weeks ago, I built a room reservation page in an APEX application for a local non-profit. I was testing the functionality when a common problem emerged.

The problem was that by default, a user was able to double-book clients and rooms. This would lead to LOTS of problems because:

  • Each client should only be able to have one current reservation
  • Each room should only ever be booked to a single client

Limiting the Results

The reservation creation form has two LOVs (clients and rooms).  We want to limit the options in the LOVs so that it only shows clients and rooms that are available.

The table structure for this example looks like:

  • Client table (Client ID, Name)
  • Room table (Room ID, Room Number)
  • Reservation table (Client ID, Room ID, Check In Date, and Check Out Date)

The two queries below return the client’s name and the room numbers as display values, respectively, with IDs for the return values.


SELECT CLIENT.NAME display_value, CLIENT.ID return_value
FROM CLIENT

SELECT ROOM.ROOM_NUMBER display_value, ROOM.ID return_value
FROM ROOM

Subquery Time

Now we can add a subquery in the WHERE clause to narrow the results of clients and rooms. The subqueries below will return a list of clients and rooms that currently have active reservations for use in the larger query. It is important to note that an active reservation will have a null check out date.


SELECT CLIENT.NAME display_value, CLIENT.ID return_value
FROM CLIENT
WHERE ID NOT IN (
SELECT CLIENT_ID
FROM RESERVATION
WHERE CHECKOUT_DATE IS NULL)

SELECT ROOM.ROOM_NUMBER display_value, ROOM.ID return_value
FROM ROOM
WHERE ROOM.ID NOT IN (
SELECT ROOM_ID
FROM RESERVATION
WHERE CHECKOUT_DATE IS NULL)

Conclusion

In summary, the WHERE clauses in these larger queries contain subqueries that check for room and client IDs attached to open reservations. When the WHERE clause is found, the client ID or room ID is kicked out of the result pool and prevents that object from being double-booked!

Learn More

You can learn more helpful tips like the ones above by visiting the Traust Consulting Blog.