Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Convert SQL Script to SAP HANA DB SCRIPT

Hi Experts,

All our reports are made for sql only, we need to deploy BIR reports on one of our clients which using HANA DB now.

We need help regarding converting the sql script to hana script.

Hope you can help us. T.I.A

Here's the sample code that we have tried to convert to hana script but we are getting error with the parameter which is date.

DROP TABLE "dfsi_rpt_ewthdr";

--[Note:CaseFixer] Table dfsi_rpt_ewthdr not found in given schemas

--[Note:Stringifier] SAP HANA does not support the GO statement

CREATE COLUMN TABLE "dfsi_rpt_ewthdr" ("CONAME" varchar(250) NULL, "COTAXID" varchar(20) NULL,

"COADDR1" varchar(500) NULL, "COADDR2" varchar(1) NULL, "COPHONE" varchar(20) NULL, "COZIP" varchar(10) NULL,

"IDVEND" varchar(50) NULL, "VENDZIP" varchar(10) NULL, "ADDR1" varchar(250) NULL, "ADDR2" varchar(250) NULL,

"VENDNAME" varchar(250) NULL, "FISCPERIOD" integer NULL, "FISCYEAR" char(4) NULL, "TAXABLEAMT" decimal(19, 6) NULL,

"TAXAMT" decimal(19, 6) NULL, "TIN" varchar(30) NULL, "cname" varchar(250) NULL, "docnum" varchar(200) NULL);

--[Note:Stringifier] SAP HANA does not support the GO statement SELECT * FROM "dfsi_rpt_ewthdr";

--[Note:CaseFixer] Table dfsi_rpt_ewthdr not found in given schemas DROP TABLE "dfsi_rpt_ewtdtl";

--[Note:CaseFixer] Table dfsi_rpt_ewtdtl not found in given schemas

--[Note:Stringifier] SAP HANA does not support the GO statement

DROP TABLE "dfsi_rpt_ewtdtl";

CREATE COLUMN TABLE "dfsi_rpt_ewtdtl" ("CONAME" varchar(250) NULL, "IDVEND" varchar(50) NULL,

"VENDNAME" varchar(250) NULL, "TAXTYPE" varchar(60) NULL, "WTName" varchar(60) NULL, "WTCode" varchar(10) NULL,

"DOCNUMBER" varchar(32) NULL, "FISCPERIOD" integer NULL, "FISCYEAR" char(4) NULL, "TAXABLEAMT" decimal(19, 6) NULL,

"TAXAMT" decimal(19, 6) NULL, "TIN" varchar(50) NULL, "cname" varchar(250) NULL);

--[Note:Stringifier] SAP HANA does not support the GO statement SELECT * FROM "dfsi_rpt_ewtdtl";

--[Note:CaseFixer] Table dfsi_rpt_ewtdtl not found in given schemas DROP VIEW "dfsi_vw_Month";

--[Note:CaseFixer] View dfsi_vw_Month not found in given schemas

--[Note:Stringifier] SAP HANA does not support the GO statement

--CREATE VIEW "dfsi_vw_Month" AS S

--SELECT '1' AS "Code", MONTH(ADD_MONTHS(ADD_DAYS('1900-01-01 00:00:00.000', -1), 1)) AS "Name" FROM DUMMY UNION ALL SELECT '2' AS "Code",

--MONTH(ADD_MONTHS(ADD_DAYS('1900-01-01 00:00:00.000', -1), 2)) AS "Name" FROM DUMMY UNION ALL SELECT '3' AS "Code",

--MONTH(ADD_MONTHS(ADD_DAYS('1900-01-01 00:00:00.000', -1), 3)) AS "Name" FROM DUMMY UNION ALL SELECT '4' AS "Code",

--MONTH(ADD_MONTHS(ADD_DAYS('1900-01-01 00:00:00.000', -1), 4)) AS "Name" FROM DUMMY UNION ALL SELECT '5' AS "Code",

--MONTH(ADD_MONTHS(ADD_DAYS('1900-01-01 00:00:00.000', -1), 5)) AS "Name" FROM DUMMY UNION ALL SELECT '6' AS "Code",

--MONTH(ADD_MONTHS(ADD_DAYS('1900-01-01 00:00:00.000', -1), 6)) AS "Name" FROM DUMMY UNION ALL SELECT '7' AS "Code",

--MONTH(ADD_MONTHS(ADD_DAYS('1900-01-01 00:00:00.000', -1), 7)) AS "Name" FROM DUMMY UNION ALL SELECT '8' AS "Code",

--MONTH(ADD_MONTHS(ADD_DAYS('1900-01-01 00:00:00.000', -1), 8)) AS "Name" FROM DUMMY UNION ALL SELECT '9' AS "Code",

--MONTH(ADD_MONTHS(ADD_DAYS('1900-01-01 00:00:00.000', -1), 9)) AS "Name" FROM DUMMY UNION ALL SELECT '10' AS "Code",

--MONTH(ADD_MONTHS(ADD_DAYS('1900-01-01 00:00:00.000', -1), 10)) AS "Name" FROM DUMMY UNION ALL SELECT '11' AS "Code",

--MONTH(ADD_MONTHS(ADD_DAYS('1900-01-01 00:00:00.000', -1), 11)) AS "Name" FROM DUMMY UNION ALL SELECT '12' AS "Code",

--MONTH(ADD_MONTHS(ADD_DAYS('1900-01-01 00:00:00.000', -1), 12)) AS "Name" FROM DUMMY;

--[Note:Modifier] Use FROM DUMMY if there is no FROM clause in the SELECT statement

--[Note:Stringifier] SAP HANA does not support the GO statement SELECT * FROM "dfsi_vw_Month";

--[Note:CaseFixer] Table dfsi_vw_Month not found in given schemas DROP PROCEDURE "dfsi_sp_formewt2307";

--[Note:Stringifier] SAP HANA does not support the GO statement

DROP PROCEDURE "dfsi_sp_formewt2307";

CREATE PROCEDURE "dfsi_sp_formewt2307"

(INOUT FromDate timestamp, INOUT ToDate timestamp) AS BEGIN FromDate := CAST(:FromDate AS varchar(10));

--[Note:Modifier] SAP HANA does not support function CONVERT; use the CAST function or implicit casting functions

ToDate := CAST(:ToDate AS varchar(10));

--[Note:Modifier] SAP HANA does not support function CONVERT; use the CAST function or implicit casting functions

DROP table "#temp";

CREATE local temporary column table "#temp" ("coname" nvarchar(200), "taxidnum" nvarchar(50), "coaddr1" nvarchar(1000),

"coaddr2" nvarchar(1000), "cophone" nvarchar(32), "cozip" nvarchar(100), "rdo" nvarchar(100), "idvend" nvarchar(100),

"addr1" nvarchar(1000), "addr2" nvarchar(1000), "vendname" nvarchar(500), "vendzip" nvarchar(100),

"fiscperiod" integer, "fiscyear" char(4), "taxableamt" decimal(19, 6),"taxamt" decimal(19, 6), "tin" nvarchar(50),

"docnum" nvarchar(50), "taxtype" nvarchar(60), "wtcode" nvarchar(50), "wtname" nvarchar(200), "cname" nvarchar(200),

"DocDate" timestamp);

DROP table "#tmp1";

CREATE local temporary column table "#tmp1" AS (SELECT D."DocEntry", A."CompnyName" AS "coname", A."TaxIdNum2" AS "TaxIdNum", A."CompnyAddr" AS "coaddr1", CAST('' AS varchar(1000)) AS "coaddr2", A."Phone1" AS "cophone", B."ZipCode" AS "cozip", A."RevOffice" AS "rdo", F."CardCode" AS "idvend", F."Address" AS "addr1", CAST('' AS varchar(1000)) AS "addr2", F."CardName" AS "vendname", IFNULL(F."ZipCode", '') AS "vendzip", MONTH(D."DocDate") AS "fiscperiod", CAST(YEAR(D."DocDate") AS char(4)) AS "fiscyear", IFNULL(J."TaxbleAmnt", 0) AS "taxableamt", IFNULL(J."WTAmnt", 0) AS "taxamt", IFNULL(F."LicTradNum", '') AS "tin", D."DocNum", I."WTName" AS "taxtype", I."WTCode", I."WTName", D."DocDate" FROM OPCH D INNER JOIN OCRD F ON D."CardCode" = F."CardCode" LEFT OUTER JOIN PCH5 J ON D."DocEntry" = J."AbsEntry" INNER JOIN OWHT I ON I."WTCode" = J."WTCode" INNER JOIN OADM A ON 1 = 1 INNER JOIN ADM1 B ON 1 = 1);

--[Note:Modifier] SAP HANA does not support function CONVERT; use the CAST function or implicit casting functions

DROP table "#tmp2";

CREATE local temporary column table "#tmp2" AS (SELECT IFNULL(J."BaseAbsEnt", 0) AS "BaseRef", A."CompnyName" AS "coname", A."TaxIdNum", A."CompnyAddr" AS "coaddr1", CAST('' AS varchar(500)) AS "coaddr2", A."Phone1" AS "cophone", B."ZipCode" AS "cozip", A."RevOffice" AS "rdo", F."CardCode" AS "idvend", F."Address" AS "addr1", CAST('' AS varchar(500)) AS "addr2", F."CardName" AS "vendname", IFNULL(F."ZipCode", '') AS "vendzip", MONTH(D."DocDate") AS "fiscperiod", CAST(YEAR(D."DocDate") AS char(4)) AS "fiscyear", IFNULL(J."TaxbleAmnt", 0) AS "taxableamt", IFNULL(J."WTAmnt", 0) AS "taxamt", IFNULL(F."LicTradNum", '') AS "tin", D."DocNum", I."WTName" AS "taxtype", I."WTCode", I."WTName", d."DocDate" FROM ORPC D

INNER JOIN OCRD F ON D."CardCode" = F."CardCode"

LEFT OUTER JOIN RPC5 J ON D."DocEntry" = J."AbsEntry"

INNER JOIN OWHT I ON I."WTCode" = J."WTCode"

INNER JOIN OADM A ON 1 = 1 INNER JOIN ADM1 B ON 1 = 1);

--[Note:Modifier] SAP HANA does not support function CONVERT; use the CAST function or implicit casting functions

DROP table "#tmp3";

CREATE local temporary column table "#tmp3" AS

(SELECT CASE WHEN a."coname" IS NULL THEN b."coname" ELSE a."coname" END AS "coname",

CASE WHEN a."TaxIdNum" IS NULL THEN b."TaxIdNum" ELSE a."TaxIdNum" END AS "TaxIdNum",

CASE WHEN a."coaddr1" IS NULL THEN b."coaddr1" ELSE a."coaddr1" END AS "coaddr1",

CASE WHEN a."coaddr2" IS NULL THEN b."coaddr2" ELSE a."coaddr2" END AS "coaddr2",

CASE WHEN a."cophone" IS NULL THEN b."cophone" ELSE a."cophone" END AS "cophone",

CASE WHEN a."cozip" IS NULL THEN b."cozip" ELSE a."cozip" END AS "cozip",

CASE WHEN a."rdo" IS NULL THEN b."rdo" ELSE a."rdo" END AS "rdo",

CASE WHEN a."idvend" IS NULL THEN b."idvend" ELSE a."idvend" END AS "idvend",

CASE WHEN a."addr1" IS NULL THEN b."addr1" ELSE a."addr1" END AS "addr1",

CASE WHEN a."addr2" IS NULL THEN b."addr2" ELSE a."addr2" END AS "addr2",

CASE WHEN a."vendname" IS NULL THEN b."vendname" ELSE a."vendname" END AS "vendname",

CASE WHEN a."vendzip" IS NULL THEN b."vendzip" ELSE a."vendzip" END AS "vendzip",

CASE WHEN a."fiscperiod" IS NULL THEN b."fiscperiod" ELSE a."fiscperiod" END AS "fiscperiod",

CASE WHEN a."fiscyear" IS NULL THEN b."fiscyear" ELSE a."fiscyear" END AS "fiscyear",

CASE WHEN b."BaseRef" IS NULL THEN a."taxableamt" WHEN a."DocEntry" = b."BaseRef" THEN a."taxableamt" - b."taxableamt" WHEN a."DocEntry" IS NULL THEN b."taxableamt" * -1 END AS "taxableamt",

CASE WHEN b."BaseRef" IS NULL THEN a."taxamt" WHEN a."DocEntry" = b."BaseRef" THEN a."taxamt" - b."taxamt" WHEN a."DocEntry" IS NULL THEN b."taxamt" * -1 END AS "taxamt",

CASE WHEN a."tin" IS NULL THEN b."tin" ELSE a."tin" END AS "tin", CASE WHEN a."DocNum" IS NULL THEN b."DocNum" ELSE a."DocNum" END AS "docnum",

CASE WHEN a."taxtype" IS NULL THEN b."taxtype" ELSE a."taxtype" END AS "taxtype",

CASE WHEN a."WTCode" IS NULL THEN b."WTCode" ELSE a."WTCode" END AS "wtcode",

CASE WHEN a."WTName" IS NULL THEN b."WTName" ELSE a."WTName" END AS "wtname", a."coname" AS "cname", a."DocDate"

FROM "#tmp1" a JOIN "#tmp2" b ON b."BaseRef" = a."DocEntry");

INSERT INTO "#temp" (SELECT * FROM "#tmp3" WHERE "DocDate" BETWEEN :FromDate AND :ToDate);

DELETE FROM "dfsi_rpt_ewthdr";

INSERT INTO "dfsi_rpt_ewthdr" (SELECT "coname", "taxidnum", "coaddr1", "coaddr2", "cophone", "cozip", "idvend", "vendzip", "addr1", "addr2", "vendname", "fiscperiod", "fiscyear", SUM("taxableamt"), SUM("taxamt"), "tin", "cname", "docnum" FROM "temp" GROUP BY "coname", "taxidnum", "coaddr1", "coaddr2", "cophone", "cozip", "idvend", "vendzip", "addr1", "addr2","vendname", "fiscperiod", "fiscyear", "tin", "cname", "docnum");

DELETE FROM "dfsi_rpt_ewtdtl";

INSERT INTO "dfsi_rpt_ewtdtl" (SELECT "coname", "idvend", "vendname", "taxtype", "wtname", "wtcode", "docnum", "fiscperiod", "fiscyear", SUM("taxableamt"), SUM("taxamt"), "tin", "cname" FROM "temp" GROUP BY "coname", "idvend", "vendname", "taxtype", "wtname", "wtcode", "docnum", "fiscperiod", "fiscyear", "tin", "cname");

SELECT * FROM "dfsi_rpt_ewthdr" ORDER BY CAST("docnum" AS bigint) ASC;

--[Note:Modifier] SAP HANA does not support function CONVERT; use the CAST function or implicit casting functions END;

--[Note:Stringifier] SAP HANA does not support the GO statement

UPDATE "dfsi_rpt_ewtdtl" set "dfsi_rpt_ewtdtl"."WTCode" = 'W' + (select "OffclCode" from "OWHT" b where b."WTCode" = "dfsi_rpt_ewtdtl"."WTCode")

from "dfsi_rpt_ewtdtl";

END;

CALL "dfsi_sp_formewt2307"('2015/01/01', '2015/31/12');

ERROR MSG:

Could not execute 'CALL "dfsi_sp_formewt2307"('2015/01/01', '2015/31/12')'

SAP DBTech JDBC: [1288]: expression cannot be used as an assignment target: '2015/01/01': line 1 col 28 (at pos 27)

Former Member
Not what you were looking for? View more on this topic or Ask a question