I have a function that is defined as:
Where XXXXX is my schema YYYYY is my package.
PROCEDURE "XXXXX"."YYYYY.SPATIAL::SP_GA_PT_PATH_DISTANCE" (IN PID NVarChar(36)) LANGUAGE SQLScript SQL SECURITY INVOKER --DEFAULT SCHEMA <default_schema_name> AS BEGIN
I want to call a function and assign the result to a variable, I have tried the following two ways:
intIntersect := XXXXX.YYYYY.SPATIAL::GA_INTERSECT (32.925148, -117.020051, -- xx 32.924672, -117.019454, -- xx 32.924488, -117.020322, -- xx 32.924849, -117.019759);
SELECT XXXXX.YYYYY.SPATIAL::GA_INTERSECT (32.925148, -117.020051, -- xx 32.924672, -117.019454, -- xx 32.924488, -117.020322, -- xx 32.924849, -117.019759) INTO intIntersect FROM DUMMY;
I have played with different permutations of this, but nothing works.
Any ideas?
Thanks.
As I explained here:
What you describe as a FUNCTION
is really a PROCEDURE
in your code example.
These differ in the ways you can call either of them. Procedures need to be called via the CALL
statement.
Functions can either be used as scalar function in all places where you can use expressions (i.e. the projection list of a SELECT
-statement) or, for table-typed functions, like a table in the WHERE
condition.
The parameters handed over to the procedure seem to be a list of data items. The general way to pass "lists" of parameters is to use a table-type parameter:
CREATE FUNCTION "XXXXX"."YYYYY.SPATIAL::SP_GA_PT_PATH_DISTANCE" (IN_PIDS TABLE (PID NVARCHAR(36)) ) RETURNS TABLE (DISTANCES DECIMAL) AS BEGIN SELECT * FROM :IN_PIDS; ...
That's of course all covered in the many SAP tutorials and trainings...
Followed Lars' advice and called function:
DOESINTERSECT := "XXXXX"."YYYYY.SPATIAL::GA_INTERSECT" (r1.TOLAT, r1.TOLONG, r1.FROMLAT, r1.FROMLONG, CURRLAT, CURRLONG, CENTERLAT, CENTERLONG);
Also, rewrote function to use ST_INTERSECT:
FUNCTION "XXXXX"."YYYYYYYYYYYY.SPATIAL::GA_INTERSECT" ( IN LAT_A1 DOUBLE, IN LONG_A1 DOUBLE, IN LAT_A2 DOUBLE, IN LONG_A2 DOUBLE, IN LAT_B1 DOUBLE, IN LONG_B1 DOUBLE, IN LAT_B2 DOUBLE, IN LONG_B2 DOUBLE) RETURNS RET_DOESINTERSECT DOUBLE LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS BEGIN DECLARE DOESINTERSECT INTEGER; SELECT NEW ST_LineString('LineString(' || LONG_A1 || ' ' || LAT_A1 || ',' || LONG_A2 || ' ' || LAT_A2 || ')').ST_Intersects(NEW ST_LineString('LineString(' || LONG_B1 || ' ' || LAT_B1 || ',' || LONG_B2 || ' ' || LAT_B2 || ')') ) INTO DOESINTERSECT FROM DUMMY; RET_DOESINTERSECT := :DOESINTERSECT; END;
Still have my select into, because complains about the use of the NEW operator with a scalar assignment. HELP tells me to try: ST_GeomFromText, as in:
SELECT ST_GeomFromText( 'LineString( 1 2, 5 7 )', 4326 ).ST_AsText() FROM dummy;
But, haven't taken the time to get this to work.
Add a comment