Question Re: Create Temporary Table with table-type in Technology Q&A

Create Temporary Table with table-type

acaireta — Wed, 06 Nov 2013 11:37:48 GMT

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.

Re: Create Temporary Table with table-type

Former Member — Wed, 06 Nov 2013 12:39:38 GMT

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

Re: Create Temporary Table with table-type

rindia — Wed, 06 Nov 2013 12:59:02 GMT

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

Re: Create Temporary Table with table-type

acaireta — Thu, 07 Nov 2013 16:46:13 GMT

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.

Re: Create Temporary Table with table-type

Trinidad — Tue, 19 Nov 2013 08:36:18 GMT

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) );

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.

Re: Create Temporary Table with table-type

acaireta — Thu, 21 Nov 2013 10:43:25 GMT

Hi,

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

Thanks for your cooperation.