How do I write executesql query to display the car hire's status based from the bookings table which has fields of hire date, return date and status.
Status has a value list of: Available, Hired, Service.
See attached for table
Assuming that the current status is reflected in whatever is the most recent Bookings* entry:
ExecuteSQL ( "
WHERE Fk_car = ?
ORDER BY Start_date DESC
FETCH FIRST 1 ROW ONLY
" ; "" ; "" ; Car::Id
Though with possible stati of Available and Service, this is not really an accurate name for that table …
Morning. I'm curious why an executeSQL statement is needed? If these two tables are already related (and I see they are through the fk field, we could use the relationship to find the information needed.
I'm guessing the last status of a booking determines the status of the car?
If I were setting this up, I'd make my relationship between these two sort by the Start Date (desc) so the most recent booking is the first in the portal or first in the relationship. Then I'd simply get the value of the status field in the related table.
Bookings::Value. Using this one field I get the value of the FIRST record in the relationship.
Thanks for replies.
I didn't explain it in detail. The car status will be based on the current date and bookings for that particular car.
Start date by desc will X days in the future.
Retrieving data ...