table type definitions for variables

[former_member184795]

Question on the table type definitions for variables.

From Rich's link here:

http://scn.sap.com/community/developer-center/hana/blog/2014/12/01/new-sqlscript-features-in-sap-han...

On HANA db version 1.00.097

Trying to declare the following table var within a SQLScript procedure:

  declare lt_vw_rslts_tab table ( MANDT NVARCHAR(3),
         DOC_NUM NVARCHAR(10),
         GUID VARBINARY(16) CS_RAW);

SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "CS_RAW": line 7 col 29 (at pos 189)

This will compile but is not what I need

  declare lt_vw_rslts_tab table ( MANDT NVARCHAR(3),
         DOC_NUM NVARCHAR(10),
         GUID VARBINARY(16) );

To test

create procedure hollas.crte_tabvar_tst LANGUAGE SQLSCRIPT AS


BEGIN


  declare lt_vw_rslts_tab table ( MANDT NVARCHAR(3),
         DOC_NUM NVARCHAR(10),
         GUID VARBINARY(16) CS_RAW);


  lt_vw_rslts_tab = select top 10 client as mandt, object_id as doc_num, guid from sapsr3.crmd_orderadm_h;


END;

These table create statements work fine

drop table hollas.tst_crte_tab;
create global temporary column table hollas.tst_crte_tab 
( MANDT NVARCHAR(3),
         DOC_NUM NVARCHAR(10),
         GUID VARBINARY(16) CS_RAW);


drop table hollas.tst_crte_tab;
create column table hollas.tst_crte_tab 
( MANDT NVARCHAR(3),
         DOC_NUM NVARCHAR(10),
         GUID VARBINARY(16) CS_RAW);

I can create a global temporary table instead and do an insert for now, just wondering if I'm doing something wrong with table type definition above?

[lbreddemann · Accepted solution]

Ok, I'm curious: what's the difference between VARBINARY(16) and VARBINARY(16) CS_RAW for you?

When I try to find differences I don't get far:

create column table rawtest ( GUID_CSRAW VARBINARY(16) CS_RAW, GUID_VARBIN VARBINARY(16));

select * from table_columns where table_name ='RAWTEST';

TABLE_NAME	COLUMN_NAME	POSITION	DATA_TYPE_ID	DATA_TYPE_NAME	OFFSET	LENGTH	CS_DATA_TYPE_ID	CS_DATA_TYPE_NAME
RAWTEST	GUID_CSRAW	1	-3	VARBINARY	8	16	82	RAW
RAWTEST	GUID_VARBIN	2	-3	VARBINARY	8	16	82	RAW

I fail to see the difference here.

The CS_RAW is an additional indication of the column store storage type.

For the functional type definition it doesn't have any impact, so you could safely leave it out.

- Lars

[former_member184795 · Accepted solution]

hi Lars, Thanks for taking the time to respond to my question.

To make a long story short, we're trying to avoid a data type conversion when joining two CRM tables, namely but000 & crmd_partner. (link is on b.partner_guid (varbinary(16) CS_RAW to c.partner_no (NVARCHAR(32))

So I was hoping I could store the results of a reduced data set containing partner_no in a table var and then join to but000 with the same data type (and avoid the data type conversion)

  declare lt_vw_rslts_tab table ( MANDT NVARCHAR(3),
:
:,
         PRIM_REP_PARTNER_GUID VARBINARY(16)
         );

A CDS view populates lt_srch_vw table var and the partner_no value from crmd_partner table is held in a field called prim_rep_pnum

        /* hold intermediary results in table var, also include guid values */

        lt_vw_rslts_tab  = Select v.*,
                                             prim_rep_pnum as prim_rep_partner_guid
                           from :LT_SRCH_VW as v;

So this change above fixes our perf issue, however we lose the primary rep information. Debugging the AMDP, I can see the issue lies with how the field prim_rep_partner_guid now contains a different value compared to the original NVARCHAR(32) field. It's doing a to_varbinary() conversion on it.

However if I bring this back to sql editor and create a test column table as such

create column table hollas.tst_crte_tab
( MANDT NVARCHAR(3),
         DOC_NUM NVARCHAR(10),
         GUID_RAW VARBINARY(16) CS_RAW);

And insert partner_no direct as so

insert into hollas.tst_crte_tab
  (MANDT,DOC_NUM,GUID_RAW)
select top 10 client as mandt, 'DUMMY_DOC' as doc_num, partner_no as guid_raw
from sapsr3.crmd_partner
where partner_no <> ''

In a roundabout way, this is why I believed I needed the CS_RAW extension.

However, as Lars suggested, the type declaration is fine, I just needed to do code a hextobin() conversion instead of the implicit to_varbinary() conversion done at runtime.

Message was edited by: Sean Holland

Message was edited by: Sean Holland