SQLScript procedure execution

[satya_sreenivasan]

Hi,

I'm on HANA version 1.00.53.375657.

I need to populate 2 tables(ins1, ins2) based on the results of some calculations on 2 other tables(tab1, tab2). I have created a procedure for this and roughly it looks like this:

create procedure proc1(IN var1 INTEGER)

LANGUAGE SQLSCRIPT as

BEGIN

     tabvar1 = select col1, col2 from tab1 where id = :var1;

     tabvar2 = select c1, c2, c3, c4 rom tab2 where <conditions>

     tabvar3 = (select P2.c1, P2.c2, P3.c3

                    from

                    (

                        select c1, c2, c3, sum(c4) from tab1 as P1, :tabvar2 as P2

                         where <conditions>

                         group by P2.c1, P2.c2, P2.c3)

                    where <conditions>);

     insert into ins1 (select :var1 as id, c1 as name from :tabvar3);

     insert into ins2 (select c1 || '_' || c2 as customers from :tabvar3);

END;

When this procedure is invoked with an input value for the first time, ins1 and ins2 have no rows in them.

When invoked with the same input value subsequently, ins1 and ins2 have the expected number of rows.

For instance,

call proc1(5)  ----> No rows in ins1 and ins2 corresponding to input 5

call proc1(5)  -> expected number of rows in ins1 and ins2.

call proc1(5000)   -> no rows in ins1 and ins2 corresponding to input 5000

call proc1(5000)  -> expected no of rows in ins1 and ins2

I had inserted the following statement into the procedure

tabvar3 = TRACE(:tabvar3)

and checked that tabvar3 indeed has no rows after the first execution (with a new input value).

Is this expected behavior?

Many thanks in advance for your suggestions/pointers.

[rindia]

Dear Satya,

No, this is not expected behavior.

In order to test your scenario, I just created one procedure not exactly as yours but some what close like this:

I am on Version: 1.0.46

DROP PROCEDURE "SRK"."PROC";
CREATE PROCEDURE "SRK"."PROC" (IN VAR1 INTEGER)
LANGUAGE SQLSCRIPT AS
BEGIN
   TABVAR1 = SELECT EID, ENAME, EDEPT, SAL, COMM FROM "SRK"."EMPLOYEE" 
                                        WHERE EID = :VAR1;
   INSERT INTO "SRK"."EMPLOYEE1" ( SELECT :VAR1 AS EID, ENAME, EDEPT, 
                  SAL, COMM FROM :TABVAR1);
END;
CALL "SRK"."PROC"(1);

When i checked the table EMPLOYEE1, a row with EID 1 is inserted successfully.

There is nothing wrong i found in your query but I suggest you to use CE functions, for example

TABVAR1 = SELECT EID, ENAME, EDEPT, SAL, COMM 
FROM "SRK"."EMPLOYEE" WHERE EID = :VAR1;

can be replaced as

T1 = CE_COLUMN_TABLE ("SRK"."EMPLOYEE");
TABVAR1 = CE_PROJECTION (:T1, ["EID","ENAME","EDEPT","SAL"], 
                                                                  '"EID" = :VAR1' );

Could you please post your complete query as there is something missing here.

Regards

Raj

[satya_sreenivasan]

Hi Raj

Thank you for your reply. Here is the procedure - it basically determines points which belong inside a polygon. I understand that can be easily accomplished with the geo-spatial API, but anyway -

drop procedure historyforzone_cp;
create procedure historyforzone_cp(IN zone_id INTEGER)
LANGUAGE SQLSCRIPT  as
zone_coord NCLOB := '';
BEGIN

zonedata = select minx, miny, maxx, maxy, COORDINATESTRING from ISR_ZONE where id = :zone_id;
select COORDINATESTRING into zone_coord from :zonedata;

--extract the (x,y) coordinates for the vertices to populate tempcoord table
call findcoord(zone_coord) with overview;

--points inside bounding box
temptable = select id, macaddress, datatimestamp, smoothedx, smoothedy from ISR_DATAPOINT d
where
  d.venuename = 'Tangs@Vivo' and
  d.floornumber = '1' and 
  d.smoothedX >= (select minx from :zonedata)  and
  d.smoothedX <=  (select maxx from :zonedata) and
  d.smoothedY >= (select miny from :zonedata)  and
  d.smoothedY <= (select maxy from :zonedata)
   ;

--Edge Ray test determines points inside zone (these have odd number of intersections with edges of the zone)

restab = (select DATAPOINT, MACADDRESS, VISITDATE
from
  (select DATAPOINT, MACADDRESS, VISITDATE, mod(intersections,2) as MOD_INT
   from
   (select P1.id as DATAPOINT, P1.macaddress as MACADDRESS, P1.datatimestamp as VISITDATE, P1.smoothedx as X, P1.smoothedy as Y, SUM(
    case when (poly1.y < P1.smoothedy AND poly2.y >= P1.smoothedy) OR (poly1.y >= P1.smoothedy AND poly2.y < P1.smoothedy)
      then case when (P1.smoothedx < ((poly2.x - poly1.x)*(P1.smoothedy -poly2.y)/(poly2.y - poly1.y)) + poly1.x)
         then 1 else 0 end
      else 0 END) as INTERSECTIONS
    FROM tempcoord as poly1, tempcoord as poly2, :temptable as P1
    where poly1.id = poly2.id - 2
    GROUP BY P1.smoothedx, P1.smoothedy, P1.id, P1.macaddress, P1.datatimestamp))
  where MOD_INT <> 0);
 
restab = TRACE(:restab);
 
insert into t1 (select :zone_id as zone_id, DATAPOINT as datapoint_id from :restab);
insert into t2 (select MACADDRESS || '_' || to_date(VISITDATE) as customers, to_varchar(:zone_id) as zonenames from :restab);
   
END;

As I had mentioned, I need to call this procedure twice to have t1 and t2 populated for a polygon.

Appreciate your guidance on what be causing this behavior.

Regards

Satya

[woutdejong]

Hi,

This really feels like Geo Spatial querying. Have you tried SP6 (rev 66+) yet? To enable the geo spatial engine: read SAP HANA One Spatial capabilities on AWS | SAP HANA .

Cheers, Wout

[stephanie_lewellen]

I understand that can be easily accomplished with the geo-spatial API

The new (SPS 07) Geo Spatial documentation is also available on the SAP HANA Help Portal:

SAP HANA Spatial Reference