Need Basic definitions .

[former_member199126]

Hi guys,

I am from ABAP , and i am new to HANA. I am trying to educate myself with this new technology.Whle going through i faced some problems in understanding some of the terms and concepts here.

Some of them are "Procedures" and "Table types". In ABAP we have some thing called as internal tables, where we fetch the data from the database table and fill it in the internal table where we use to manipulate the data and upload back in to the Database and commit it.Are they something like internal tables ??

Going more in to my question , please clarify me what is a procedure and a table type(Also please add if there are ank key terms that are a prerequsite to be known before jumping in to app development in HANA) and  what do they do.

I have gone through the SQL script reference guide , but i was not able to find out clear definitions.

:  i want this thread to be more useful to the people who try to educate themselves in HANA and lets start from the basic definitons what HANA has. Experts plese help.

[former_member184768 · Accepted solution]

Hi Karthikeyan,

Welcome to the world of HANA. 🙂

Before I try to explain your doubts, let me touch upon one basic point.

The key difference between processing in ABAP and processing in Procedure is, ABAP processing happens on the data fetched from the database, whereas Procedure (or Database Procedure) processing happens IN the database itself.

Procedure is nothing but processing of data or implementation of business logic / validation / data manipulation performed on the data fetched from the database tables. As mentioned, the processing happens in the database itself and hence is very fast.

In ABAP, you fetch the data, store in the internal tables in the application layer, manipulate the data and then write it back to the database.

Procedure manipulates the data directly in the tables and hence is much faster as compared to ABAP processing.

Table Types are similar to Internal table in ABAP, but do not contain any data. It is like defining the structure in ABAP, but not creating an instance of the structure to store the data.

I am sure you will get more replies and this discussion will evolve.

Regards,

Ravi

[former_member199126 · Accepted solution]

Thank you for the response. i was able to understand the diff between ABAP and HANA.There is something called SQL editor.is that the only place where we code our program ?

Can you tell things about the SQL editor? I saw that more than one script editor can be created. How to they communicate each other.

In ABAP we do have predefined functions.Is there any thing like that in HANA. If so can you give an examlple ??

[former_member184768 · Accepted solution]

Hi Karthikeyan,

The Procedures can be edited and created from the SQL editor. My preference (old habit from Oracle world) is to write the procedure in a standard text editor and create it in the database with DROP,  CREATE or ALTER command.

There are useful predefined functions for data manipulation like Date and Time calculations, String data manipulation etc (ADD_YEARS, ADD_MONTHS, SECONDS_BETWEEN), LAST_DAY, NEXT_DAY). Please refer to the SQL reference manual for the details.

You can also define your own functions and can reuse them in other functions / procedures.

BTW, Functions and Procedures are similar to what you have in SE37 and SE38.

Please let me know what specific information are you looking for and I am sure I'll be able to help you with the same.

Regards,

Ravi

[former_member199126 · Accepted solution]

Thank you for the quick response .

MY doubt is with the SQL editor(not script editor , i mentioned it wrong previously).

i saw that more than one SQL editors can be created.how do they communicate each other ? Moreover what is the necessity of having more than one SQL editor ??? if once the table type is created, is it stored permanantly somewhere and  is DROP  the only way to delete it?

[former_member184768 · Accepted solution]

Ya, I got that about script editor.

The SQL Editor is nothing but a connection to the HANA database. It establishes a user session with the DB. Multiple sessions need not communicate with each other,  but if you need to create a Table type which can be stored to the database then definitely you can do that.

When you create any object in the database with CREATE statement, the object persists in the database. The Object once created can be used by any client process which has access to the object. Now if you open two different SQL Editors with same user id, then the object created in one session (Object creation is a DDL - Data Definition Language activity, which fires implicit commit, at least in Oracle, I am sure even in HANA) with the DDL statement like below:

CREATE TYPE {schema.}name AS TABLE (name1 type1 {, name2 type2,...})

the Table type will be created in the database and will be available in the other editor.

If the other editor is using a different connection to the database with different user id, then the user id should have access to the object created in the first user schema.

DROP is used to delete the object permanently. You can use ALTER to change it anytime.

Regards,

Ravi

[Former Member · Accepted solution]

You can delete the table using the wizard, by going to the context menu of the table from "Catalog" and it gives you a option, if you want to delete the "data" or "definition" or "both".

Regarding multiple editors,

Example: In your landscape,if you have DEV,QUALITY and PROD. And you add them into your HANA Studio which is possible, and if you want to open a SQL editor of each of the different systems in your landscape this option would be helpful as it opens in that particular server instance.

Apart from it , in a single server instance it gave me more flexibility to try different SQL statements in different editors,

But i too need to know about the other benefits (If any) of using more editors.

Regards,

Krishna Tangudu

[former_member199126 · Accepted solution]

Thanks Ravi ,

in the procedure,

what is OUT FINAL_TABLE TT_ARTICLE_LOOKUP mean ??? is final_table is of type tt_article_lookup ?

my create procedure was success.Moving forward my insert satatement does not work.Please tell me what mistake did i make in that statement

T_ARTICLE_LOOKUP has three fields namely ARTICLE_ID , CATEGORY, SALE_PRICE.

[former_member184768 · Accepted solution]

Hi Karthikeyan,

I think there is a confusion between table type and table. INSERT INTO would work with TABLE and not with TABLE TYPE.

Table Type is like STRUCTURE and not INTERNAL TABLE in ABAP. So you cannot insert the data into the Structure.

If you need to store the data, you might want to create TABLE and not TABLE TYPE.

Regards,

Ravi

[former_member199126 · Accepted solution]

Ravi ,

I guess i did not state my problem correctly.

Please look the above screnshot . The CREATE PROCEDURE is working fine. But after that i used INSERT INTO "P1152511"."T_ARTICLE_LOOKUP" (which is a table) statement which is giving me the error.

Please look the bottom of the screen for the error. I want to know what mistake did i make in that statement.Can you plese correct it ?

[former_member184768 · Accepted solution]

Hi Karthikeyan,

2 points:

1) Are you trying to use T_ARTICLE_LOOKUP or TT_ARTICLE_LOOKUP. Somehow I think one T is missing.

2) You cannot perform INSERT INTO statement on TABLE TYPE.

Please find below the comment from the SQL Reference manual.:

"In contrast to definitions of database tables, table types do not have an instance that is created when the table type is created (i.e., no Data Manipulation Language (DML) statements (INSERT, UPDATE, DELETE) are supported on table types)."

Regards,

Ravi

[former_member199126 · Accepted solution]

No, It is not a table type

I used INSERT INTO T_ARTICLE_LOOKUP VALUES(1,'SHIRT',500);  which worked fine.

The thing which i require is i need to select a custom records from the database table("EFASHION_TUTORIAL"."ARTICLE_LOOKUP) and populate in the custom DB table(T_ARTICLE_LOOKUP) which i have created.

[rama_shankar3 · Accepted solution]

Karthikeyan:

You can also delete using SQL in SQL editor. i.e:

delete from user.article_color_lookup;   - deletes all records from the respective table.

Regards,

Rama

[rama_shankar3 · Accepted solution]

Karthikeyan:

Table types are variable structures by themselves that

have a group of fields with respective data types. You can not define variables for table

types or insert data into table type directly. You define table types outside the procedure using SQL and then use the table types

in the procedure as in or out parameter.

In your scenario, you will use table type as output parameter of the procedure.

If you need a sample script, I can look for a sample usage .

Regards,

Rama

[former_member184768 · Accepted solution]

I think you are trying to approach the problem in ABAP way.

Basically you need data from table A based on some conditions (custom records) and put into table B. In ABAP you will perform this in the following manner:

1) Select data from Table A into internal table (int_Table_A TYPE Table_A) defined on the structure of table A.

2) Modify the records from the internal table and assign them to another internal table (int_Table_B TYPE Table_B).

3) Write int_Table_B to Table B.

In SQL, you don't need to do these. Rather you can write a statement like

INSERT into TABLE B as SELECT col1, col2, col3 from TABLE_A "a" where a.col1 = some_Value_1 and a.col2 = some_value_2;

This can also be achieved in a stored procedure, if you want to persist the logic of defining the custom record generation (values of some_value_1 and some_value_2 or any other derivation / business logic) and can be created in a simple procedure without any parameters. If you need to provide the values of "some_value_1" and some_value_2 as input variables of the procedure, then I'd suggest refer to the simple procedure sample code which takes two input variables which can be used inside the procedure.

No need to create Internal tables and complicate life. 🙂

Regards,

Ravi

[former_member199126 · Accepted solution]

Thank you Ravi.  My doubts are getting cleared now.Now i have learnt that we do not have to use a table type to insert data in to custom table rather we ca use a select query directly.

Regarding the insert query sample that you told me,

   i wrote it as,

  INSERT INTO TABLE "P1152511"."T_ARTICLE_LOOKUP" AS

   SELECT ARTICLE_ID, CATEGORY, SALE_PRICE FROM "EFASHION_TUTORIAL"."ARTICLE_LOOKUP" WHERE SALE_PRICE > 1000;

  And i tried this too..

INSERT INTO TABLE "P1152511"."T_ARTICLE_LOOKUP" AS

   SELECT ARTICLE_ID, CATEGORY, SALE_PRICE FROM "EFASHION_TUTORIAL"."ARTICLE_LOOKUP" "A" WHERE A.SALE_PRICE > 1000;

and it says incorrect syntax near "P1152511"."T_ARTICLE_LOOKUP".Can you please tell me where did I go wrong?

For ur info "P1152511"."T_ARTICLE_LOOKUP" has three fields namely ARTICLE_ID , CATEGORY and SALE_PRICE.  

The SQL script that we use in HANA is completely different of what we use in ABAP. SO struggling a bit to get the concepts of that.  

[former_member199126 · Accepted solution]

Thank you Rama ,

I would like to have a sample script of insert delete and modify statements. That would really help.

i need bulk insert statements ( for ex: i need to select article from table A where price is > 1000 and save it in another table).

Thanks,

[former_member184768 · Accepted solution]

Hi Karthikeyan,

Please refer to my reply below. I'd recommend you refer to the SQL script manual. It has the sample code examples.

As I said, bulk insert can be done with INSERT INTO ... AS SELECT ... statement and Simple insert (INSERT INTO .... ), delete (DELETE FROM... ) and modify (UPDATE .... ) can be performed in the procedure as mentioned in the SQL script manual.

Regards,

Ravi

[former_member184768 · Accepted solution]

Please try the following

INSERT INTO "P1152511"."T_ARTICLE_LOOKUP"  SELECT ...

Without TABLE and AS in the syntax.

It is also helpful if you provide the column list like

INSERT INTO "P1152511"."T_ARTICLE_LOOKUP" (ARTICLE_ID, CATEGORY, SALE_PRICE)

   SELECT ARTICLE_ID, CATEGORY, SALE_PRICE FROM "EFASHION_TUTORIAL"."ARTICLE_LOOKUP" WHERE SALE_PRICE > 1000;

Ravi

[former_member184768 · Accepted solution]

I tried the following which works:

create table t (a int);

create table t1 (b int);

insert into t values (1);

insert into t1 (b) select a from t;

select * from t1;

Ravi

[former_member199126 · Accepted solution]

Thank You so much Ravi... 🙂 It is working..

[former_member184768 · Accepted solution]

Glad that I could help. Can you please close this thread.

Its been a long discussion.

Thanks and regards,

Ravi

[Former Member]

This message was moderated.

[former_member199842]

This message was moderated.

[Former Member]

Hello Karthik,

Table Type is something which we use in "Parameters" in our SQL Scripts. You we define "data types" to indicate the type of data using "data types" in parameters to define the signature of the "function". In the same way if you want to send a "Table" as a parameter, we ned to define a global "Table Type".

Regards,

Krishna Tangudu