Create Temporary Table with table-type

[acaireta]

In HANA is possible to create a temporary table with the structure already defined in a TYPE?

EXAMPLE: 

CREATE TYPE MY_TYPE as TABLE(
              col1 varchar(1),
               col2 integer)
CREATE LOCAL TEMPORARY TABLE #TMP_TBL WITH MY_TYPE

Fore more information, I have HANA PLATFORM Rev. 53.

Thanks very much.

[rindia]

Hi Adria,

What Isaias suggested is correct.

Below is the syntax which is tested on revision 58.

If you want to explore more on this, refer to link

http://help.sap.com/hana/html/sql_create_table.html#create_table_contents_source

Regards

Raj

[acaireta]

Hi,

Your answer is correct but now when generating the temporary table with TYPE I skip other error:

CASE 1 -> works.

DROP PROCEDURE MY_PROC_0;
CREATE PROCEDURE MY_PROC_0( in itemcode nvarchar(20))
LANGUAGE SQLSCRIPT AS
BEGIN
          CREATE LOCAL TEMPORARY TABLE #ITEMS --LIKE MY_TYPE;
                              (  col1 nvarchar(20),  
               col2 nvarchar(20),
               _isitem varchar(1) );  
          SELECT * FROM #ITEMS WHERE _isitem is NULL;
          DROP TABLE #ITEMS;
END;

CASE 2 -> Returns error: SAP DBTech JDBC: [7] (at 249): feature not supported: no result column: line 9 col 9 (at pos 249)

DROP TYPE MY_TYPE;
CREATE TYPE MY_TYPE as TABLE(  
              col1 nvarchar(20),  
               col2 nvarchar(20),
               _isitem varchar(1) );  
DROP PROCEDURE MY_PROC_0;
CREATE PROCEDURE MY_PROC_0( in itemcode nvarchar(20))
LANGUAGE SQLSCRIPT AS 
BEGIN
          CREATE LOCAL TEMPORARY TABLE #ITEMS LIKE MY_TYPE;
                              /*(  col1 nvarchar(20),  
               col2 nvarchar(20),
               _isitem varchar(1) );*/  
          SELECT * FROM #ITEMS WHERE _isitem is NULL;
          DROP TABLE #ITEMS;
END;

CASE 3 -> Return this error message: SAP DBTech JDBC: [260] (at 270): invalid column name: _ISITEM: line 9 col 30 (at pos 270)

DROP TYPE MY_TYPE;
CREATE TYPE MY_TYPE as TABLE(  
              col1 nvarchar(20),  
               col2 nvarchar(20),
               _isitem varchar(1) );  
DROP PROCEDURE MY_PROC_0;
CREATE PROCEDURE MY_PROC_0( in itemcode nvarchar(20))
LANGUAGE SQLSCRIPT AS 
BEGIN
          CREATE LOCAL TEMPORARY TABLE #ITEMS LIKE MY_TYPE;
                              /*(  col1 nvarchar(20),  
               col2 nvarchar(20),
               _isitem varchar(1) );*/  
          SELECT '' FROM #ITEMS WHERE _isitem is NULL;
          DROP TABLE #ITEMS;
END;

For both 2 and 3 appears to be as if the temporary table should not consider any column, as I can fix it?

Thanks very much.

[Trinidad]

Hi Adria,

Have you tried to put “_isitem” between double quotes?

1.  DROP TYPE MY_TYPE;  

2.  CREATE TYPE MY_TYPE as TABLE(    

3.                col1 nvarchar(20),    

4.                 col2 nvarchar(20),  

5.                 “_isitem” varchar(1) );   

6.                   

7.   

8.   

9.  DROP PROCEDURE MY_PROC_0;  

10. CREATE PROCEDURE MY_PROC_0( in itemcode nvarchar(20))  

11. LANGUAGE SQLSCRIPT AS   

12. BEGIN 

13.           CREATE LOCAL TEMPORARY TABLE #ITEMS LIKE MY_TYPE;       

14.           SELECT '' FROM #ITEMS WHERE “_isitem” is NULL;  

15.     

16.           DROP TABLE #ITEMS;  

17. END; 

Saludos,

Trinidad.

[acaireta]

Hi,

I just realized the HANA PLATFORM UPGRADE to Rev. 67 and it seems that it works perfectly.

Thanks for your cooperation.

[Former Member]

Hi,

I'm without an env to test, but you can try to use LIKE instead WITH.

CREATE LOCAL TEMPORARY TABLE #TMP_TBL LIKE MY_TYPE

Best regards