Skip to Content

Call Scalar Function From HANA Procedure

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.

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • Posted on Aug 27 at 01:04 AM

    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...

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Sep 01 at 07:23 PM

    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
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.