Oracle Datatype to HANA Datatype Mapping

Oracle data type	SAP HANA data type
INTEGER	DECIMAL
NUMBER	DECIMAL
NUMBER(19)-NUMBER(38)	DECIMAL
NUMBER(10)-NUMBER(18)	BIGINT
NUMBER(5)-NUMBER(9)	INTEGER
NUMBER(2)-NUMBER(4)	SMALLINT
NUMBER(1)	TINYINT
NUMBER(p,s)	DOUBLE (if s > p),                                        DECIMAL (if 0 < s <= p),                             SMALLINT (if s < 0 and p-s <= 4),               INTEGER (if s < 0 and 4 < p-s <= 9),           BIGINT (if s < 0 and 9 < p-s <= 18),           DECIMAL (if s< 0 and p-s > 18)
	DECIMAL (if 0 < s <= P)
	SMALLINT (if s < 0 and p-s <= 4)
	INTEGER (if s < 0 and 4 < p-s <= 9)
	BIGINT (if s < 0 and 9 < p-s <= 18)
	DECIMAL (if s< 0 and p-s > 18)
FLOAT	DOUBLE
FLOAT(1)-FLOAT(24)	REAL
FLOAT(25)-FLOAT(126)	DOUBLE
BINARY_FLOAT	REAL
BINARY_DOUBLE	DOUBLE
DATE	TIMESTAMP
TIMESTAMP(n)	TIMESTAMP
CHAR	VARCHAR
NCHAR	NVARCHAR
VARCHAR2	VARCHAR ( MAX LENGTH : 5000)
NVARCHAR2	NVARCHAR
BLOB	BLOB
BFILE	BLOB
RAW	VARBINARY
LONG	CLOB
LONG RAW	BLOB
CLOB	CLOB/NCLOB
NCLOB	NCLOB
INTERVAL	VARCHAR
TIMESTAMP WITH TIME ZONE	VARCHAR
TIMESTAMP WITH LOCAL TIME ZONE	VARCHAR
ROWID	Not Supported.. Its Pseudocolumn $row_id$
UROWID	Not Supported
ANYDATA	Not Supported
VARRAY	Not Supported
NESTEDTAB	Not Supported
OBJECT	Not Supported
REF	Not Supported
XMLANY	CLOB

Thanks for visiting My Blog...

Share your Comments...

SAP HANA: Insert into with Select Statement

Example 1:

To insert a record for a Particular Columns in a table:

Code:

INSERT INTO "KABIL_PRACTICE"."PROC_TEST" ("ID","NAME") (SELECT 1,'KABIL' FROM DUMMY);

Example 2:

To insert a record in a table:

Code:

INSERT INTO "KABIL_PRACTICE"."PROC_TEST" (SELECT 1,'KABIL' FROM DUMMY);

INSERT INTO "KABIL_PRACTICE"."PROC_TEST" SELECT 1,'KABIL' FROM DUMMY;

Example 3:

To insert a record in a table with some Calculations:

Code:

INSERT INTO "KABIL_PRACTICE"."PROC_TEST" VALUES (1, TO_CHAR(ADD_DAYS(CURRENT_DATE,1)));

Thanks For Visiting My Blog...

Share your comments...

SAP HANA: Table Functions

Table Functions are now the recommended script-based artefact whenever a modelling requirement cannot be fulfilled with graphical information views.

In previous versions of SAP HANA, it was possible to create scripted calculation views, which are similar in their key principles but provide less flexibility and performance. In particular, Table User Defined Functions allow an improved movement of filters from client tools down to the source data.

You create design-time Table Functions with the SAP HANA Developer perspective.

It is also possible to convert existing Scripted Calculation Views into Table Functions, with a migration tool available in SAP HANA Studio since SAP HANA SPS11.

Note:

Table functions can return only a single result set.

Table functions are read-only functions. This means that you cannot use insert, update, delete, commit, or exec SQL statement inside a table function. If you have any of these SQL statements in a table function, it will not activate.

Table Functions

• are side-effect free read-only functions that use standard SQL

• can be used as source data in Graphical Calculation Views

• consume data from tables, models, functions, predictive algorithms, etc.

EXAMPLES:

To start, as always, make sure you are in the Development Perspective.

Step 1:  From the Developer Perspective, choose Repositories tab.

Step 2: Select the package where you want to create a Table Function, Right-click the package selects New and choose Other.

Step 3: This brings up the new window and select Table Function and click Next.

Step 4: Now provide a name to this table function. Here I named it as a TBL_FN_DEMO click anywhere outside the box .hdbtablefunction will automatically appended to the name and Click Finish.

Step 5: This opens up a table function with some default code already present and makes up the skeleton of the overall code which is always needed.

Firstly, let’s clean up the comments placed by the system. Remove them by deleting the text between BEGIN and END block.

 Step 6: Let’s understand these rows one by one. The FUNCTION keyword marks the beginning of a SAP HANA function.

"USER2"."KABIL_PRACTICE::TBL_FN_DEMO" ( )

This keyword is followed by the auto-generated complete name of the table function which is a combination of the

USER2 = username

KABIL_PRACTICE = Package Name

TBL_FN_DEMO = Table Function Name

        The set of open and closed brackets “( )” at the end is used to add any Input parameters to this table function. For now, we will leave it blank inside.

RETURNS return_table_type

         The next line starts with a RETURNS keyword. The return_table_type is just a placeholder for the actual code. This is similar to the columns folder in the Scripted view where you specify the list of output fields for the Scripted node.

        We need to remove this return_table_type and provide the structure and datatype of the output we expect from this table function.

        As seen below, we add the keyword TABLE to specify that the output of this function is a table and it will have 4 fields

        Below the BEGIN Keyword we have to write RETURN Keyword. This tells HANA that the select statement you write below this would be the output of this table function.

Step 7: Now, we are required to write the select statement which provides output in the same structure and order as the RETURNS TABLE statement.

Now, write your logic below the RETURN statement as shown as below:

CODE:

FUNCTION "USER2"."KABIL_PRACTICE::TBL_FN_DEMO" ( )

      RETURNS Table(

      "Order_ID" Decimal(8,1),

      "Order_Date" Date,

      "Order_Quantity" Decimal(5,1),

      "Sales" Decimal(31,13)

      )

      LANGUAGE SQLSCRIPT

      SQL SECURITY INVOKER AS

BEGIN

RETURN

select      "Order_ID",

            "Order_Date",

            "Order_Quantity",

            "Sales"

from "KABIL_PRACTICE"."SUPERSTORE_SALES";

END;

Step 8: Finally validate and activate the Table Function.

             you can now notice that an active table function is now available in your package.

Step 9: To check the data consistency of this table function, select Modeler Perspective, from the SQL Console run a below query:

select * from "USER2"."KABIL_PRACTICE::TBL_FN_DEMO" ( ) ;

 Result:

Thank you for visitng My Blog....

Share Your Comments

SAP HANA: Functions in Procedures

Example: Sales Procedure

Scenario:

            Whenever I enter a record into the sales table, it will affect the other tables based on that entry. Such as if I make an entry in “SALES_PROC” for 20 PEN sold it will affect the “PRODUCT_MASTER_PROC” table Stock.

            And also “SALES_AMOUNT” column in “SALES_PROC” table will be calculated automatically based “USP” (Unit Selling Price) column in a “PRODUCT_ITEM_PROC” table.

Let’s see for details, here I used 3 tables named as "PRODUCT_MASTER_PROC", "PRODUCT_ITEM_PROC" and "SALES_PROC" as shown below:

Table 1: Product_Master_Proc

Table 2: PRODUCT_ITEM_PROC

Table 3: SALES_PROC

CODE:

To create a PRODUCT_MASTER_PROC table:

create column table "KABIL_PRACTICE"."PRODUCT_MASTER_PROC"

(

"P_ID" integer Primary Key,

"P_NAME" nvarchar(35),

"C_ID" integer,

"Category" nvarchar(35),

"Stock" integer

);

To insert a record in a PRODUCT_MASTER_PROC table:

insert into "KABIL_PRACTICE"."PRODUCT_MASTER_PROC" values (1,'PEN',1,'STAIONARY',150);

insert into "KABIL_PRACTICE"."PRODUCT_MASTER_PROC" values (2,'NOTEBOOK',1,'STAIONARY',100);

insert into "KABIL_PRACTICE"."PRODUCT_MASTER_PROC" values (3,'CHAIR',2,'FURNITURE',150);

insert into "KABIL_PRACTICE"."PRODUCT_MASTER_PROC" values (4,'TABLE',2,'FURNITURE',150);

insert into "KABIL_PRACTICE"."PRODUCT_MASTER_PROC" values (5,'FAN',3,'ELECTRICALS',150);

insert into "KABIL_PRACTICE"."PRODUCT_MASTER_PROC" values (6,'LIGHT',3,'ELECTRICALS',150);

To create a PRODUCT_ITEM_PROC table:

create column table "KABIL_PRACTICE"."PRODUCT_ITEM_PROC"

(

"P_ID" integer primary key,

"P_NAME" nvarchar(35),

"C_ID" integer,

"USP" Decimal (34,2),

foreign key("P_ID") references "KABIL_PRACTICE"."PRODUCT_MASTER_PROC"("P_ID")

);

To insert a record in a PRODUCT_ITEM_PROC table:

insert into "KABIL_PRACTICE"."PRODUCT_ITEM_PROC" values (1,'PEN',1,10);

insert into "KABIL_PRACTICE"."PRODUCT_ITEM_PROC" values (2,'NOTEBOOK',1,30);

insert into "KABIL_PRACTICE"."PRODUCT_ITEM_PROC" values (3,'CHAIR',2,150);

insert into "KABIL_PRACTICE"."PRODUCT_ITEM_PROC" values (4,'TABLE',2,1000);

insert into "KABIL_PRACTICE"."PRODUCT_ITEM_PROC" values (5,'FAN',3,650);

insert into "KABIL_PRACTICE"."PRODUCT_ITEM_PROC" values (6,'LIGHT',3,100);

To create a SALES_PROC table:

create column table "KABIL_PRACTICE"."SALES_PROC"

(

"S_ID" integer primary key generated by default as IDENTITY,

"P_ID" integer,

"P_NAME" nvarchar(35),

"QTY" integer,

"Sales_AMount" Decimal(34,2),

foreign key ("P_ID") References "KABIL_PRACTICE"."PRODUCT_ITEM_PROC" ("P_ID")

);

TABLE TYPE:

A table type is

·         Similar to a database table but we can’t insert/update/delete. It contains only structure.

·         Used to define parameters for a procedure that represent tabular results.

To create a Table Type:

create type "KABIL_PRACTICE"."TT_SALES_PRODUCT_INVENTORY" as table

(

"S_ID" integer primary key,

"P_ID" integer,

"P_NAME" nvarchar(35),

"QTY" integer,

"Sales_AMount" Decimal(34,2)

)

To Create a Procedure:

create procedure "KABIL_PRACTICE"."PROC_SALES_INVENTORY"

(

IN "P_ID" integer,

IN "P_NAME" nvarchar(35),

IN "QTY" integer,

OUT Result "KABIL_PRACTICE"."TT_SALES_PRODUCT_INVENTORY"

)

language SQLSCRIPT

as begin

declare PROD_ID integer;

declare INV integer;

declare EP decimal (34,2);

declare SALES_AMOUNT Decimal(34,2);

select count(*) into PROD_ID from "KABIL_PRACTICE"."PRODUCT_ITEM_PROC" where "P_ID" = :P_ID;

select "Stock" into INV from "KABIL_PRACTICE"."PRODUCT_MASTER_PROC" where "P_ID" =:P_ID;

select "USP" into EP from "KABIL_PRACTICE"."PRODUCT_ITEM_PROC" where "P_ID" =:P_ID;

IF INV >= :QTY THEN

SALES_AMOUNT := (:QTY * :EP);

insert into "KABIL_PRACTICE"."SALES_PROC" ("P_ID","P_NAME","QTY","Sales_AMount") values (:P_ID,:P_NAME,:QTY,:SALES_AMOUNT);

update "KABIL_PRACTICE"."PRODUCT_MASTER_PROC" set "Stock" = "Stock" - :QTY where  "P_ID" = :P_ID;

END IF;

Result = select * from "KABIL_PRACTICE"."SALES_PROC";

end;

To Call a Procedure:

call "KABIL_PRACTICE"."PROC_SALES_INVENTORY" (1,'PEN',30,?);

 Note:

Here, I make an entry by mentioning “Product_Id”, “Product_Name” and “Quantity” alone.

Result:

Let check the “PRODUT_MASTER_PROC” table

Now check the “PRODUCT_ITEM_PROC and “SALES_PROC” Table

How to Use Function in Procedure:

            From the above, we can see the “SALES_AMOUNT” is calculated by performing multiplication of quantity with a unit selling price. The same logic can be implemented by using the function.

Example:

To Create a Function:

create function "KABIL_PRACTICE"."SALES_AMOUNT"

 ( Q integer, USP integer)

 returns result decimal (34,2)

 language SQLSCRIPT

 SQL SECURITY INVOKER as

 Begin

 result := :Q * :USP;

 END;

To use function in Procedure:

create procedure "KABIL_PRACTICE"."PROC_SALES_INVENTORY_EHE"

(

IN "P_ID" integer,

IN "P_NAME" nvarchar(35),

IN "QTY" integer,

OUT Result "KABIL_PRACTICE"."TT_SALES_PRODUCT_INVENTORY"

)

language SQLSCRIPT

as begin

declare PROD_ID integer;

declare INV integer;

declare EP decimal (34,2);

declare SALES_AMOUNT Decimal(34,2);

DECLARE empty_name CONDITION FOR SQL_ERROR_CODE 10001;

DECLARE EXIT HANDLER FOR empty_name

begin

SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE FROM DUMMY;

end;

select count(*) into PROD_ID from "KABIL_PRACTICE"."PRODUCT_ITEM_PROC" where "P_ID" = :P_ID;

select "Stock" into INV from "KABIL_PRACTICE"."PRODUCT_MASTER_PROC" where "P_ID" =:P_ID;

select "USP" into EP from "KABIL_PRACTICE"."PRODUCT_ITEM_PROC" where "P_ID" =:P_ID;

IF INV >= :QTY THEN

SALES_AMOUNT := "KABIL_PRACTICE"."SALES_AMOUNT"(:QTY,:EP);

(or)

/*--select "KABIL_PRACTICE"."SALES_AMOUNT"(:QTY,:EP) into SALES_AMOUNT from dummy;---*/

insert into "KABIL_PRACTICE"."SALES_PROC" ("P_ID","P_NAME","QTY","Sales_AMount") values (:P_ID,:P_NAME,:QTY,:SALES_AMOUNT);

update "KABIL_PRACTICE"."PRODUCT_MASTER_PROC" set "Stock" = "Stock" - :QTY where  "P_ID" = :P_ID;

END IF;

Result = select * from "KABIL_PRACTICE"."SALES_PROC";

end;

Note:

The highlighted area shows the various ways to use function in HANA Procedures.

Thank you for Visiting...

Share your comments...