on 03-22-2017 11:46 PM
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));
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
80 | |
11 | |
8 | |
8 | |
6 | |
6 | |
6 | |
5 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.