Skip to Content
0
Former Member
Sep 17, 2013 at 08:15 AM

How to escape keywords in Local Table Types definition of Stored Procedures?

368 Views

Hi fellow developers!

I am writing a simple Stored procedure:

CREATE PROCEDURE getOemsByVehicleId (

in VehicleId nvarchar(10),

out oem_by_vehicle tt_oem_equipment

)

LANGUAGE SQLSCRIPT

SQL SECURITY INVOKER

READS SQL DATA AS

BEGIN

oem_by_vehicle =

SELECT M1."PRODUCT_GROUP", E1."MATERIAL_ID", E1."MARKET", E1."FROM", E1."TO"

FROM "SHOWCASE_00"."d290.showcase.Showcase.data::equipment" AS E1

JOIN "SHOWCASE_00"."d290.showcase.Showcase.data::material" AS M1 ON M1."MATERIAL_ID" = E1."MATERIAL_ID"

WHERE E1."VEHICLE_ID" = :VehicleId AND E1."MATERIAL_ID" NOT IN (

SELECT DISTINCT E."MATERIAL_ID"

FROM "SHOWCASE_00"."d290.showcase.Showcase.data::vehicle" AS V

JOIN "d290.showcase.Showcase.data::AV_CONSTRAINT_SETS" AS AV1 ON V."CONS_SET_ID" = AV1."CONS_SET_ID"

JOIN "SHOWCASE_00"."d290.showcase.Showcase.data::equipment" AS E ON V."VEHICLE_ID" = E."VEHICLE_ID"

JOIN "d290.showcase.Showcase.data::AV_CONSTRAINT_SETS" AS AV2 ON E."CONS_SET_ID" = AV2."CONS_SET_ID"

JOIN "SHOWCASE_00"."d290.showcase.Showcase.data::material" AS M ON E."MATERIAL_ID" = M."MATERIAL_ID"

JOIN "d290.showcase.Showcase.data::AV_CONSTRAINT_SETS" AS AV3 ON M."CONS_SET_ID" = AV3."CONS_SET_ID"

WHERE V."VEHICLE_ID" = :VehicleId AND (

(AV1."NAME" = AV2."NAME" AND AV1."VALUE" != AV2."VALUE") OR

(AV1."NAME" = AV3."NAME" AND AV1."VALUE" != AV3."VALUE") OR

(AV2."NAME" = AV3."NAME" AND AV2."VALUE" != AV3."VALUE")

)

)

ORDER BY M1."PRODUCT_GROUP", E1."MATERIAL_ID";

END;

In the other tab called "Local Table Types" I defined my returned table type:

create type tt_oem_equipment as table (

PRODUCT_GROUP nvarchar(20),

MATERIAL_ID nvarchar(10),

MARKET nvarchar(20),

"FROM" date,

"TO" date

);

The procedure with this table type won't be activated. After some testing I figured that the compiler parses the creation of the tabletype like that (taken from error message):

create type "_SYS_BIC"."d290.showcase.Showcase.models/getOemsByVehicleId/tabletype/tt_oem_equipment" as table ( "PRODUCT_GROUP" NVARCHAR(20), "MATERIAL_ID" NVARCHAR(10), "MARKET" NVARCHAR(20), ""FROM"" DATE, ""TO"" DATE );

So it seems my quotation marks are being doubled thus leading to parsing error when creating the table type. On the other hand, if I write it without the quotation marks to prevent the doubling, I already see in HANA studio that it will not activate as the keywords FROM and TO are not used properly.

The only way I found to work around that is to name the columns not as they are supposed to be named (e.g. name it FROM1). But then I need to rename the columns in the Select-Statement as well. And then I will need to adjust my XSJS-Service which will call this service. That's a little inconvenient.

So here my question: isn't there a way to escape the keywords "FROM" and "TO" in a way that I can name the columns in the Local Table Type like that? After all it works perfectly fine in SQL-Statements and the definition of static table types. But the SAP HANA SQL Reference for the CREATE TYPE statement (http://help.sap.com/hana/html/sql_create_type.html) doesn't mention any way to escape keywords, the SAP HANA SQLScript Reference just refer to the other guide in this question.

Any hints from you?

Many thanks for every helpful answer 😊

Philipp