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