Skip to main content

#nepali #newari #somali #basotho #nebraskan #nederlander

binit.prads.net

binitprads

binitprads

kathmandu.im

pradsprads

binit

+BinitPradhanang

 

Creating a table in Teradata

1 min read

1. Creating a Volatile table / permanent table first and then inserting into it 

Ex:

````

STEP1: 

CREATE VOLATILE TABLE ,

NO FALLBACK, NO JOURNAL, NO LOG

(COLUMN1 DEF)

PRIMARY INDEX (COLUMN1)

ON COMMIT PRESERVE ROWS;

 ````

STEP2:

```` 

INSERT INTO (

SELECT COLUMN1 FROM TABLE2

);

 ````

 

2. Creating a table on the fly using a SELECT statement results 

````

CREATE VOLATILE TABLE ,

NO FALLBACK, NO JOURNAL, NO LOG

AS (

SELECT COLUMN1 FROM TABLE2

) WITH DATA

PRIMARY INDEX (COLUMN1)

ON COMMIT PRESERVE ROWS;

````

 

https://forums.teradata.com/forum/database/difference-between-create-table-statements

 

create set table with index and also setting date format to yyyy-mm-dd

CREATE SET TABLE T1
(
COL1 INTEGER NOT NULL DEFAULT 0,
COL2 DATE NOT NULL FORMAT 'YYYY-MM-DD' DEFAULT DATE '2000-01-01')
UNIQUE PRIMARY INDEX ( COL1 );

 

getting left() function equivalent

SELECT CAST(column AS CHAR(5)) FROM table

 

get year, month, hour etc from date

EXTRACT (MINUTE FROM CURRENT_TIME)

EXTRACT (SECOND FROM CURRENT_TIME)

EXTRACT (HOUR FROM CURRENT_TIME)

EXTRACT (DAY FROM CURRENT_DATE)

EXTRACT (YEAR FROM CURRENT_DATE)

EXTRACT (MONTH FROM CURRENT_DATE)

 

getting year and month in yyyy-mm format from date field
substr(cast (date_field as char(10)),1,7)