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.
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
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)
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!
You can learn more helpful tips like the ones above by visiting the Traust Consulting Blog.