INVOKE in Subqueries
In addition to allowing INVOKE in a main query statement, INVOKE is also allowed in subqueries. This opens up new possibilities since the INVOKE can then, for example, easily be used in the WHERE clause. For example:
SELECT '' Violation
WHERE budget > ( INVOKE SAP.MaxBudget )
Some erroneous situations can be checked at define time, for example if the INVOKE has more or less output columns than requested. Other errors will occur at runtime, for example if the INVOKE returns multiple records where only 0 or 1 record is expected.
The following statement displays all the flights that are scheduled to 'VENICE', and where the scheduled departure time lies after a certain time. The time is read real-time using a SOAP service of the KLM.
SELECT s.scheduled_departure_time, k.flight_nr, k.flight_name
FROM scheduled_flights s, klm_flight_info k
WHERE s.flight_id = k.id
AND s.destination = 'VENICE'
AND scheduled_departure_time > (INVOKEKLMSoapService.GetDepartureTime
FROM klm_flight_info k2
WHERE k2.id = 21
ORDER BY scheduled_departure_time