SAP HANA execute query generated within a procedure

Question

I am new with SAP HANA, I am trying to generate a query and execute it within a stored procedure.

I got an error and I am not sure that HANA can do something like that.

Here my code

    CREATE PROCEDURE "PROCEDURE_IBA_TESTCSV"(
            IN SCHEMA_NAME VARCHAR(100))
LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS
BEGIN
DECLARE T VARCHAR(1000);
DECLARE TA VARCHAR(1000);
 SELECT
  ' SELECT ' || MAX(C_1) || IFNULL(MAX(C_2),'')|| IFNULL(MAX(C_3),'') ||' AS STATEMENT FROM ' || SCHEMA_NAME || '.' || TABLE_NAME || ' ' INTO T
 FROM ( 
 SELECT POSITION, DATA_TYPE_ID, COLUMN_NAME ,SCHEMA_NAME, TABLE_NAME , 
 CASE WHEN POSITION = 1 THEN        
' CASE WHEN ( '|| COLUMN_NAME ||' IS NULL ) THEN '''' ELSE REPLACE(TO_CHAR(' || COLUMN_NAME || '),''.'','','') END'  END AS C_1, 

CASE WHEN POSITION = 2 THEN  '||''#''|| CASE WHEN ( '|| COLUMN_NAME ||' IS NULL ) THEN '''' ELSE TO_NVARCHAR('||COLUMN_NAME||') END'  END AS C_2,
 CASE WHEN POSITION = 3 THEN '||''#''|| CASE WHEN ( '|| COLUMN_NAME ||' IS NULL ) THEN '''' ELSE TO_NVARCHAR('||COLUMN_NAME||') END' END AS C_3 
  FROM ( 
 select SCALE,SCHEMA_NAME,position,TABLE_NAME,column_name, data_type_id from TABLE_COLUMNS where
  schema_name ='IMPORT_KT_STAMM_IK_348BA_20160706' AND TABLE_NAME='CLS_220_KTHISTORIE')) group by SCHEMA_NAME,TABLE_NAME; 
  
execute immediate :T  ;

INSERT INTO Test SELECT  :T from DUMMY;

END;

With execute :T I get this output

I would like to store SUM(length) of this output into a variable within the same procedure.

Is that possible ? Any help ?

thanks in advance

score 1 · Answer 1 · answered Dec 14 '16 at 06:54

1

Ok, now I understand, where this is going. As you want to work with tables of different shape, you won't be able to avoid dynamic SQL altogether. But since you always melt it into a single column, you could simply store that transformed data into, say a temporary table, and run the SUM(LENGTH(()) on that.

Not sure though why you want to go through this rather painful exercise, instead of simply exporting the data into some folder and checking the resulting size there.

answered Dec 14 '16 at 06:54

Lars Br.

9,949
2
15
29

Actually our client would like to know size of csv before export. Yes the question is hoy can i store that transformed data into a table. When I use execute immediate i get the data als output of the procedure but i can't store it in table i tried with insert into table ( execute ... ) doesn't work too :( – Houssem Hariz Dec 14 '16 at 10:42

score 0 · Answer 2 · edited May 23 '17 at 11:53

0

I don't quite get why you use dynamic SQL here. With dynamic SQL (exec/execute immediate) you don't get access to the result set. Alternatively you can use cursors. You can provide parameters for SCHEMA_NAME and TABLE_NAME and be 'dynamic' that way.

I guess this question is related to SAP HANA getting csv data size right?

edited May 23 '17 at 11:53

Community

1
1

answered Dec 12 '16 at 20:35

Lars Br.

9,949
2
15
29

Hello Lars, thank you for your reply, yes exactly i want to try to generate sql that gives me a table with csv data , then i make a sum of length for this table, I did 3 example and i got a good result, exactly like the size of csv data. Can you please help me ? when i make execute :T I get the csv table. I don't really need dynamic SQL, i only need to get the output of :T into a variable and make sum(length). thanks – Houssem Hariz Dec 13 '16 at 20:11
can I maybe write DECLARE CURSOR c_products FOR :T ? – Houssem Hariz Dec 13 '16 at 20:14

score 0 · Answer 3 · answered Dec 14 '16 at 15:36

0

I modified my code: I write "insert into Table SELECT " instead "SELECT" in line 8 and now I get data in table

answered Dec 14 '16 at 15:36

Houssem Hariz

55
3
12

SAP HANA execute query generated within a procedure

3 Answers3