I want to create a Function that outputs a CHAR value. I managed to create a SELECT statement that outputs only the column and row of that one particular value I need. However, when I try to use this function I get an error code stating that the output value is not valid (SQLCODE=-946-Result set not permitted) My function looks like this:
ALTER FUNCTION "DBA"."findLocation"(@airplane INTEGER)
RETURNS char(3)
BEGIN
SELECT TOP 1 airport FROM flights
WHERE arrivalTime <= now() and airplaneID = @airplane
ORDER BY arrivalTime DESC
END
As you can see the function is fairly simple. I want to only output the location of a plane that can be determined by getting the last airport that the plane has been(comparing right now with the arrival times). The table "flights" looks like this:
Airport (char(3)) | arrivalTime (timestamp) | airplaneID (integer)
MUC | 2016-01-01 15:00:00.123456 | 1
LAX | 2016-02-10 15:00:00.123456 | 1
STG | 2015-01-01 15:00:00.123456 | 1
MIA | 2016-01-05 15:00:00.123456 | 2
I execute the function with:
findLocation(1)
or
SELECT findLocation(1) FROM flights
The desired output would be MUC as it is as of now (2016-02-01) the last Airport. LAX is in the future. STG is before MUC and MIA can be ignored because its another plane.
The solution to this might be simple but I can't wrap my head around it. I also tried SET commands and DECLARE. I probably use it wrong though. Sybase SQL Anywhere 12 is used
Thank you two for your help! I finally got it working. And yes you where right about the explicit return and I also had to add an AS. Took me ages to try the AS...turns out it was crucial.
ALTER FUNCTION "DBA"."findLocation"(@airplane integer)
RETURNS char(3)
AS
BEGIN
DECLARE @location char(3)
SET @location = (
SELECT TOP 1 airport FROM flights
WHERE arrivaltime <= now() AND airplaneID = @airplane
ORDER BY arrivaltime desc)
RETURN @location
END