SAP HANA QUERY HELP

[millicentdark]

Hi Experts,

I have written a stored procedure for HANA but i have a small challenge with the query below; Result is right but the temporary table i created wont allow me to run the query more than once.

My suggestion is the temp table will be dropped in the query after the select statement so that the query can be run more than once;

CREATE PROCEDURE "StaffReport2"

(

  IN dateFrom date

  , IN dateTo date

)

AS

BEGIN

  CREATE LOCAL TEMPORARY TABLE #TEST_TABLE2

  (

  "TransId" int

  ,"PrjCode" nvarchar(10)

  ,"PrjName" nvarchar(100)

  ,"Account" nvarchar(15)

  ,"AcctName" nvarchar(100)

  ,"RefDate" timestamp

  ,"Memo" nvarchar(50)

  ,"Debit" decimal(21,6)

  ,"Credit" decimal(21,6)

  ,"BatchNum" int

  ,"CreateDate" timestamp

  ,"TaxDate" timestamp

  ,"Ref2" nvarchar(100)

  ,"FCDebit" decimal(21,6)

  ,"FCCredit" decimal(21,6)

  ,"DueDate" timestamp

  ,"Debit_BF"  decimal(21,6)

  ,"Credit_BF" decimal(21,6)

  ,"CardCode"  nvarchar(15)

  ,"CardName" nvarchar(100)

  ,"U_PR_TYPE"  nvarchar(100)

  );

    INSERT INTO #TEST_TABLE2

  SELECT  T2."TransId", T0."PrjCode", T0."PrjName", T1."Account",

  T3."AcctName", T1."RefDate", T2."Memo", T1."Debit", T1."Credit", T1."BatchNum", T2."CreateDate",

  T2."TaxDate",

  T1."Ref2", T1."FCDebit", T1."FCCredit", T1."DueDate", 0.0 AS "Debit_BF",

  0.0 AS "Credit_BF", T4."CardCode", T4."CardName" , T0.U_PR_TYPE

    FROM OPRJ T0

    INNER JOIN JDT1 T1 ON T0."PrjCode" = T1."Project"

    INNER JOIN OJDT T2 ON T1."TransId" = T2."TransId"

    INNER JOIN OACT T3 ON T1."Account" = T3."AcctCode"

    INNER JOIN OCRD T4 ON T4."CardCode" = T1."ShortName"

    WHERE T1."RefDate" BETWEEN :dateFrom AND :dateTo

  AND T0.U_PR_TYPE = 'Employee'

    AND T3."AcctCode" = '2001010952'

    ORDER BY T0."PrjCode", T2."TransId";

  UPDATE  #test_table2

  SET #test_table2."Debit_BF" = "DebitSum"

  , #test_table2."Credit_BF" = CASE WHEN "DebitSum" = 0 THEN 0 ELSE "CreditSum" END

    FROM #test_table2

    JOIN (

    SELECT "PrjCode", "Account", "CardCode", SUM(IFNULL("Debit", 0)) AS "DebitSum",

        SUM(IFNULL("Credit", 0)) AS "CreditSum"

      FROM #test_table2

      WHERE "RefDate" < :dateFrom

    GROUP BY "PrjCode", "Account", "CardCode") AS b

    ON

  #test_table2."PrjCode" = b."PrjCode"

     AND #test_table2."Account" = b."Account"

  AND #test_table2."CardCode" = b."CardCode";

  SELECT *   

  FROM #test_table2

    ORDER BY "PrjCode", "TransId";

END;

--//  CALL "StaffReport2"( '20100101', '20161231' )

Below is the image when i try to run the query more than once;

any help to assist me run this query more than once will be appreciated.

Regards

Justice.

[former_member1334]

Hi,

Include drop statement inside your procedure logic post your final select statement.

Example :

CREATE PROCEDURE DROPTBL()    

AS

BEGIN

CREATE LOCAL TEMPORARY TABLE #TEST_TABLE3

(

   "TransId" int

  ,"PrjCode" nvarchar(10)

  );

  INSERT INTO #TEST_TABLE3 VALUES(1,'ABC');

  SELECT * FROM #TEST_TABLE3;

  DROP TABLE  #TEST_TABLE3;

END;

Regards,

Charles

[anindya_bose]

Hi Justice

Can you try  a drop statement just after your select statement.

SELECT * 

  FROM #test_table2

    ORDER BY "PrjCode", "TransId";

END;

DROP TABLE #test_table2 ;

Or is that something you want to avoid for some reason ?

Regards

Anindya

[millicentdark]

Hi Anindya,

i did try the drop at the end of the procedure below and i got the error message below;

CREATE PROCEDURE "StaffReport3"

(

  IN dateFrom date

  , IN dateTo date

)

AS

BEGIN

  CREATE LOCAL TEMPORARY TABLE #TEST_TABLE3

  (

  "TransId" int

  ,"PrjCode" nvarchar(10)

  ,"PrjName" nvarchar(100)

  ,"Account" nvarchar(15)

  ,"AcctName" nvarchar(100)

  ,"RefDate" timestamp

  ,"Memo" nvarchar(50)

  ,"Debit" decimal(21,6)

  ,"Credit" decimal(21,6)

  ,"BatchNum" int

  ,"CreateDate" timestamp

  ,"TaxDate" timestamp

  ,"Ref2" nvarchar(100)

  ,"FCDebit" decimal(21,6)

  ,"FCCredit" decimal(21,6)

  ,"DueDate" timestamp

  ,"Debit_BF"  decimal(21,6)

  ,"Credit_BF" decimal(21,6)

  ,"CardCode"  nvarchar(15)

  ,"CardName" nvarchar(100)

  ,"U_PR_TYPE"  nvarchar(100)

  );

    INSERT INTO #TEST_TABLE3

  SELECT  T2."TransId", T0."PrjCode", T0."PrjName", T1."Account",

  T3."AcctName", T1."RefDate", T2."Memo", T1."Debit", T1."Credit", T1."BatchNum", T2."CreateDate",

  T2."TaxDate",

  T1."Ref2", T1."FCDebit", T1."FCCredit", T1."DueDate", 0.0 AS "Debit_BF",

  0.0 AS "Credit_BF", T4."CardCode", T4."CardName" , T0.U_PR_TYPE

    FROM OPRJ T0

    INNER JOIN JDT1 T1 ON T0."PrjCode" = T1."Project"

    INNER JOIN OJDT T2 ON T1."TransId" = T2."TransId"

    INNER JOIN OACT T3 ON T1."Account" = T3."AcctCode"

    INNER JOIN OCRD T4 ON T4."CardCode" = T1."ShortName"

    WHERE T1."RefDate" BETWEEN :dateFrom AND :dateTo

  AND T0.U_PR_TYPE = 'Employee'

    AND T3."AcctCode" = '2001010952'

    ORDER BY T0."PrjCode", T2."TransId";

  UPDATE  #test_table3

  SET #test_table3."Debit_BF" = "DebitSum"

  , #test_table3."Credit_BF" = CASE WHEN "DebitSum" = 0 THEN 0 ELSE "CreditSum" END

    FROM #test_table3

    JOIN (

    SELECT "PrjCode", "Account", "CardCode", SUM(IFNULL("Debit", 0)) AS "DebitSum",

        SUM(IFNULL("Credit", 0)) AS "CreditSum"

      FROM #test_table3

      WHERE "RefDate" < :dateFrom

    GROUP BY "PrjCode", "Account", "CardCode") AS b

    ON

  #test_table3."PrjCode" = b."PrjCode"

     AND #test_table3."Account" = b."Account"

  AND #test_table3."CardCode" = b."CardCode";

  SELECT *   

  FROM #test_table3

    ORDER BY "PrjCode", "TransId";

END;

DROP TABLE #test_table3;

[anindya_bose]

My mistake... what I meant is to have a drop statement just before exiting the procedure.

Your temp table is not known out side procedure, that is why you are getting the error .

BEGIN. --- Start of Procedure

...................

SELECT *...

..................

DROP TABLE #test_table3;  ---- Dropping temp table before exiting the procedure.

END ; --- End of Procedure