How can I use temp tables to get SELECT with GROUP BY in procedure

[Former Member]

Dear all,

My HANA is SPS08.

I am trying to write a procedure where there is a

Tab2 = SELECT POINT_1 AS POINT, COUNT(*) AS NUMM FROM "TRAFFIC"."PAIRSPD" WHERE POINT_2 = :PN2[:I]  GROUP BY POINT_1 ORDER BY NUMM DESC;

the Error message is:

SAP DBTech JDBC: [1307]: unsupported datatype is used: Unsupported data type

there is an link :

said it's a bug.

But if I delete  "Tab2 =" , it is fine.

The whole code:

DROP PROCEDURE TRAFFIC.ARRAY_AGG_TEST;

CREATE PROCEDURE TRAFFIC.ARRAY_AGG_TEST()

  LANGUAGE SQLSCRIPT   AS

BEGIN

  DECLARE PN2 INT ARRAY;

  DECLARE PN1 INT ARRAY;

  DECLARE TEMPN INT ARRAY;

  DECLARE I INT;

  DECLARE J INT;

  TAB1 = SELECT DISTINCT(POINT_2) AS POINT FROM "TRAFFIC"."PAIRSPD";

  PN2 := ARRAY_AGG(:TAB1.POINT ORDER BY POINT);

    I:=1;

    TAB1 = SELECT POINT FROM (SELECT POINT_1 AS POINT, COUNT(*) AS NUMM FROM "TRAFFIC"."PAIRSPD" WHERE POINT_2 = :PN2[:I]  GROUP BY POINT_1 ORDER BY NUMM DESC);

END;

Thanks a lot!

Shu

[Former Member · Accepted solution]

After Google and rewrite it:

It looks like the problem is WHERE POINT_2 = :PN2[:I]

If I change it to be " POINT_2 = 2762 "  instead of an array number. it will be fine.

So how to deal with this? Can I use the Array number as a parameter.

Thanks,

Shu

[former_member210482 · Accepted solution]

Hi shu,

Are you sure point_2 is of type integer?

Regards,

Safiyu

[Former Member · Accepted solution]

Hi Safiyu,

Actually, I am not sure about this. You hit the key point! Thanks a lot!

According to the definition it is integer. Then I tried to use the cursor to deal with this, it showed the same error. So I use the To_int to make sure the data type. It worked with the Cursor now.

CREATE PROCEDURE "TRAFFIC"."CURSOR_EXAMPLE" ( )

    LANGUAGE SQLSCRIPT

    SQL SECURITY INVOKER

    AS

BEGIN

DECLARE CURSOR c_products FOR

SELECT DISTINCT(POINT_2) AS POINT

        FROM "TRAFFIC"."PAIRSPD"

        ORDER BY POINT;

    DECLARE PN2 INT ARRAY;

DECLARE PN1 INT ARRAY;

DECLARE TEMPN1 INT ARRAY;

DECLARE TEMPN2 INT ARRAY;

DECLARE NUMM INT ARRAY;

DECLARE I INT :=1;

DECLARE J INT;

TAB1 = SELECT DISTINCT(POINT_2) AS POINT FROM "TRAFFIC"."PAIRSPD";

PN2 := ARRAY_AGG(:TAB1.POINT ORDER BY POINT);

FOR cur_row as c_products DO

   TAB2 =

    SELECT

      TO_INT(POINT_1) AS PN1,

      TO_INT(COUNT(*)) AS NUMM

    FROM

      "TRAFFIC"."PAIRSPD"

    WHERE

      POINT_2 = cur_row.POINT

    GROUP BY POINT_1

    ORDER BY COUNT(*) DESC;

    TEMPN1 := ARRAY_AGG(:TAB2.PN1);

    TEMPN2 := ARRAY_AGG(:TAB2.NUMM);

    PN1[:I] := :TEMPN1[1];

    NUMM[:I] := :TEMPN2[1];

    I := :I+1;

  END FOR;

    RST = UNNEST(:PN1,:PN2,:NUMM) AS ("POINT1","POINT2","NUMM");

    INSERT INTO "TRAFFIC"."RELATION_OF_POINTS"

    SELECT * FROM :RST;

END;

Shu

[former_member210482 · Accepted solution]

Hi Shu,

You Welcome. These kind of errors usually occur when there are some datatype mismatch. Thats why i pointed it out.

Cheers,

Safiyu