Skip to Content
0

SAP HANA Table Calculated field

Mar 22, 2017 at 11:46 PM

420

avatar image
Former Member

I have table A which has First Name and Last Name as fields with ClientId as Primary Key,

	 CREATE COLUMN TABLE NAME("FIRSTNAME" VARCHAR (50) null,
	 "LASTNAME" VARCHAR (50) null,
	 "SRNO" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 100 INCREMENT BY 1));

	 

Need to add a calculated field 'FullName' (FirstName + Last Name) at table level so don't have to created calculated column in calculation view. The table has around 40 million records.

CREATE COLUMN TABLE NAME ("FIRSTNAME" VARCHAR (50) null,
	 "LASTNAME" VARCHAR (50) null,
"FULLNAME" VARCHAR (100) null (Need this field as a calculated field i.e FIRSTNAME +LASTNAME) ,
	 "SRNO" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 100 INCREMENT BY 1));

	 
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
avatar image
Former Member Mar 23, 2017 at 04:41 AM
0

Here you go...

--Create Table 
CREATE COLUMN TABLE "HANAUSER"."NEWTABLE"(
     "FIRSTNAME" VARCHAR (50) null,
	 "LASTNAME" VARCHAR (50) null,"SRNO" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 100 INCREMENT BY 1)
	 );
	 
--Insert
insert into "HANAUSER"."NEWTABLE" values('NICK','DURAN');
insert into "HANAUSER"."NEWTABLE" values('H','CLITON');
insert into "HANAUSER"."NEWTABLE" values('D','TRUMP');
insert into "HANAUSER"."NEWTABLE" values('M','GUID');

--Select
Select * from "HANAUSER"."NEWTABLE";

Alter table add calculated field

--Alter table with calculated field 
ALTER TABLE "HANAUSER"."NEWTABLE" add ( "FULLNAME" VARCHAR(30) GENERATED ALWAYS AS ("FIRSTNAME" ||"LASTNAME"));


--Select
Select * from "HANAUSER"."NEWTABLE";

My finding generated column is way better than calculated column if data volume is very large, In my case its just a concat of two column pretty straight forward.

Thanks


newtable.jpg (25.2 kB)
newtable1.jpg (29.9 kB)
Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Mar 23, 2017 at 12:06 AM
0

I am loading table with SLT and I can add a concat field at the time of loading through SLT but don't want to change SLT at this point of time.

Yes I can do the same in Information view i.e In Calculation View as a calculated column but then it will use analytical engine as well as olap engine and will create a temp table which will impact performance.

If I can add at the table level without changing anything in SLT which I can easily do in other database it will be awesome.

I am looking for Default something like that

Share
10 |10000 characters needed characters left characters exceeded
Jan Van Ansem Mar 22, 2017 at 11:51 PM
0

Not sure what you are trying to do here.

You have a create statement for a table, but you haven't specified how you are going to populate the data.

Are you uploading it from file, creating it from another table?

Anyway, I wouldn't bother to create a column which physically holds the concatenation of two fields.

You can save yourself some space and concatenate the two fields in a view, either a SQL or Calculation view, with a simple formula.

Share
10 |10000 characters needed characters left characters exceeded