Pivot view of key value table

 select

userid,

max(case when name = 'EMAIL' then val else 0 end) as email,

max(case when name = 'PHONE' then val else 0 end) as phone,

max(case when name = 'DOB' then val else 0 end) as dob

from key_value_table

group by userid

serial rank in select statement

sel
a,
b,
rank() over(order by c)
from table1

Teradata SQL Regular Expression to compare date format

Verify if date is in format dd-MMM-yyyy

select
 regexp_similar(a_date,'^(([0-9])|([0-2][0-9])|([3][0-1]))\-(JAN|FEB|MAR|APR|MAY|JUN|JUL|AUG|SEP|OCT|NOV|DEC)\-\d{4}$') = 1
from table1

WITH clause in Teradata SQL

WITH CTE1 AS (select current_date as today)
select today
from CTE1;

Ref: http://dwgeek.com/teradata-with-clause-syntax-usage-and-examples.html/

Teradata - Ways to find queries executed

select * from dbc.qrylogsql
where collecttimestamp=date
and sqltextinfo like '%customer_tb%' -- table name

Single character wildcard _

where desc like '%-abc__f%'

Example: abcdefgh

One way of checking if date was formatted well

Date format expected: dd-MMM-yyyy. Example: 03-MAR-2017

WHERE condition:
....
and a_date is not null
and length(a_date ) = 11
and index(a_date , '-') = 3

Covert string to valid integer

select cast(coalesce(to_number('1a2'), -1) as int)

Inner Join

Will list rows where left and right match

select
emp.id,
emp.name,
dept.name
from employee emp
inner join department dept
on emp.dept_id = dept.dept_id

Will list only those employees who have a department which is found in the dept table.

Right Join

Every rows from right table and matching records from left

select
emp.id,
emp.name,
dept.name
from employee emp
right join department dept
on emp.dept_id = dept.dept_id

Even if the department doesn't have employees, it will be listed. If an employee doesn't have a department, it will not be listed.

Left Join

Every rows from left table and matching records from right

select
emp.id,
emp.name,
dept.name
from employee emp
left join department dept
on emp.dept_id = dept.dept_id

Even if the employee doesn't have a department, he/she will be listed here.

Replace

SELECT oreplace('Hello There','Hello','Hi')

This converts Hello There to Hi There.

Teradata: Extract month and year from date

 extract (year from date1)

 extract (month from date1)

Teradata: Backup table with data

create table table1_bkup as
table1 with data

Teradata: List columns of a table

select
*
from dbc.columns
where tablename ='table1'
and databasename = 'database1'

Teradata: List tables in database

select
*
from dbc.tables
where databasename='database'
and tablekind = 'T'

Teradata: Add column with default value

alter table table1 add col1 int default 0

Teradata: Rename table

rename table table1 to table2

Teradata: Add column

alter table table1 add x int;

Teradata SQL: Drop column

alter table table1 drop col1