INVOKE in Subqueries

See Also

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

FROM    Department

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.

Example

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

       WITH

       SELECT    k2.klm_flight_nr

       FROM    klm_flight_info k2

       WHERE    k2.id = 21

       )

ORDER BY scheduled_departure_time