Release Mload in Teradata

While inserting data from multi load into teradata table, multi load may fail and your main table may get locked. This lock can be in Acquisition phase or in Application phase.

To release the lock which is in Acquisition phase:-

RELEASE MLOAD TABLE_NAME;

To release the lock which is in Application phase:-

RELEASE MLOAD TABLE_NAME IN APPLY;


 

RTRIM in Teradata

We can trim extra spaces from right hand side from a string using RTRIM function:-

SELECT RTRIM('This Is Me.   ');

Output is:- This is me. (Without trailing spaces)

LTRIM in Teradata

Using LTRIM, We can remove extra spaces from a string from the left hand side of the string:-

SELECT LTRIM('  This Is Me.');

Output is:- This Is Me. (without leading spaces)

Trim in Teradata

We can trim a character in teradata as shown below:-

If you want to trim particuler character from both the end (Leading and Trailing) then you need to use 'BOTH' keyword:-

SELECT TRIM (BOTH '1' FROM '12341');

Output is:- 234

You can trim leading character from a string:-

SELECT TRIM (LEADING '1' FROM '11234');

Output is:- 234

You can trim trailing character from a string:-

SELECT TRIM (TRAILING '4' FROM '12344');

Output is:- 123

Add column in table in Teradata

We can add column in existing table in Teradata. But you may require it in the middle of the table of it is okey if you add it at the last of the table. When you required to add it in the middle of the table then you need to take a backup of table and then drop the table and recreate it and again insert the data from the backup table. But if you want to add column in the last of the table then you can simply add this by simple altering the definition of the table. Let's see how we can do it:-

ALTER TABLE TABLE_NAME ADD COLUMN_NAME DATA_TYPE (VALUE_LENGTH);

Let's say you have EMPLOYEE table and you need to add column ADDRESS column into it then you can do it like:-

ALTER TABLE EMPLOYEE ADD ADDR VARCHAR(20);

You can add multiple column in a table:-

Let's say you need to add ADDRESS AND PHONE NUMBER column to employee table then you can do it like:-

ALTER TABLE EMPLOYEE ADD ADDR VARCHAR(20), PHONE_NO INTEGER;

Creating Global Temporary Table in Teradata

When you need to perform some DML (Data Manipulation Language) operation and then want to delete all the data from the table but don't want to DROP the table as you may require it for next time to use, you can create GLOBAL TEMPORARY TABLE in teradata which delete all the data from the table when you log out from the session but keeps the definition of the table intact.

Let's see how we can create Global Temporary Table:-

CREATE GLOBAL TEMPORARY TABLE TABLE_NAME
(
COLUMN_NAME DATA_TYPE;
COLUMN_NAME DATA_TYPE;
COLUMN_NAME DATA_TYPE;
COLUMN_NAME DATA_TYPE;
.
.
)
 ON COMMIT PRESERVE ROWS;

Note:- When you are creating global temporary table and forget to mention ON COMMIT PRESERVE ROWS then you would not be able to see the data as teradata create global temporary table as ON COMMIT DELETE ROWS by default. So, work accordingly as per your requirement.

Creating Volatile table in teradata

Volatile table is a table when you log out, table's definition and data gets vanished. It is like temporary tables except temporary tables definition remains in the data dictionary whereas volatile tables gets vanished.
Let's see how to create volatile table in teradata:-

CREATE VOLATILE TABLE VOL_TABLE_NAME
(
 COLUMN_NAME DATA_TYPE,
COLUMN_NAME DATA_TYPE,
COLUMN_NAME DATA_TYPE,
COLUMN_NAME DATA_TYPE,
.
.
)
ON COMMIT PRESERVE ROWS; 

Note:- When you are creating volatile table and forget to mention ON COMMIT PRESERVE ROWS then you would not be able to see the data as teradata create volatile table as ON COMMIT DELETE ROWS by default. So, work accordingly as per your requirement.

Find out list of tables in Teradata

We can fetch all the tables present in teradata database using below query:-

SELECT * FROM DBC.TABLES WHERE TABLEKIND ='T';

 Below command will show you the number of tables in all the Databases:-

SELECT  COUNT(*) FROM DBC.TABLES WHERE TABLEKIND ='T';

If you want to find the table list for a particular database then just add another where condition to it:-

SELECT * FROM DBC.TABLES WHERE TABLEKIND ='T' AND DATABASENAME ='UAT';

Below command will show you the number of tables in particular Databases:-

SELECT * FROM DBC.TABLES WHERE TABLEKIND ='T' AND DATABASENAME ='UAT';


As shown above you can find out Views, Procedures etc by altering where condition.

Assign sequence number to variable in Oracle

In Oracle, Upto 10G, We need to SELECT the sequence next value from DUAL table. But in Oracle 11G onwards, We can directly assign a sequence next value to a variable which will avoid round trip to the Oracle server. Let's see how it is:-

First create a sequence in Oracle:-

CREATE SEQUENCE TEST_SEQ
START WITH 1
MIN VALUE 1
INCREMENT BY 1
MAX VALUE 9999999999999999
NO RECYCLE
NO CACHE;

Now, in your block, declare a variable in DECLARE section and assign value to that variable in EXECUTABLE section:-

DECLARE
SEQ_VALUE NUMBER;
BEGIN
SEQ_VALUE :=TEST_SEQ.NEXTVAL;
END;

Above code will directly assign the sequence number to variable without selecting the value from DUAL table.