Teradata SQL sampling a limited set of rows

select * from table sample 100

How to find Teradata version?

select * from dbc.dbcinfo

Teradata database storage space computation

SELECT
SUM(CurrentPerm)/(1024*1024*1024) (DECIMAL(15,6)) (TITLE 'Current Perm size in GB') ,
SUM(MaxPerm)/(1024*1024*1024) (DECIMAL(15,6)) (TITLE 'Max Perm size in GB')
FROM dbc.DiskSpace WHERE DatabaseName='a_database';

Teradata SQL Aggregate functions example

select
car_key,
sum(deprn_amt) sum_dep,
avg(accum_deprn_amt) avg_dep,
max(accum_deprn_amt) max_dep,
min(accum_deprn_amt) min_dep,
count(*) cnt
from fact_car
having sum_dep != max_dep and cnt >10 and cnt < 20 and max(nk_deprn_run_ts) > date '2014-06-01'
group by asset_key
order by max_dep desc

Teradata SQL TOP function

select top 5 * from person_tb where create_date > date '2014-10-19'

Forward vs Reverse Proxy server

The concept about Forward and Reverse proxy is explained well in this blog. http://www.jscape.com/blog/bid/87783/Forward-Proxy-vs-Reverse-Proxy

Different types of VPN

Different types of VPNs:
1. Site-to-Site VPN,
2. Dynamic VPN (DMVPN),
3. Client VPN,
4. SSLVPN
is explained clearly in this blog. http://reggle.wordpress.com/2012/03/05/different-types-of-vpn-explained/

Oracle Trunc function

Run on 8/18/2014
select trunc(sysdate, 'YYYY') from dual -> 01-JAN-14
select trunc(sysdate, 'MM') from dual -> 01-AUG-14
select trunc(sysdate, 'DD') from dual -> 18-AUG-14

Date in Teradata SQL condition

select * from payments where pay_dt > date '2014-01-01'

CHECK constraint

CONSTRAINT arrive_date_check CHECK (a_date >= d_date)

Quoting: http://www.info.teradata.com/HTMLPubs/DB_TTU_14_00/index.html#page/Database_Management/B035_1094_111A/ch12.053.31.html

Timestamp format

select count(*) from table1 where last_updt_ts > cast('2014-01-01:00:00:00' as timestamp format 'yyyy-mm-dd:hh:mi:ss')

Teradata macro

Quoting from reference: http://www.teradatahelp.com/2010/09/macro-is-teradata-extension-to-ansi-sql.html

A macro is a Teradata extension to ANSI SQL that contains pre written SQL  statements. Macros are used to run a repeatable set of tasks.The details of macro can be found in data dictionary (DD)  . Macros are database objects and thus they belong to a specified user or database. A macro can be executed by Queryman. ,  BTEQ, by another macro. 


How to create a Macro
Create a macro to generate a DOB list for department 321:

CREATE MACRO DOB_Details AS 
(SELECT  first_name ,last_name  ,DOB
FROM TERADATA.employees
WHERE dept_numbr =321 
ORDER BY DOB asc;);

EXECUTE a Macro 
To execute a macro, call it along with  the exec command. 
EXEC DOB_Details; 

last_name    first_name    DOB
Ram            Kumar         75/02/22 
Laxman       Sinha           79/04/06 

Coalesce function

If the field is null, the coalesce function can assign a default value:

select coalesce(type_desc, 'Unknown'), count(*)
from person
group by type_desc

SQL Case statement

select  
pos_num as "Position number",
case
        when status = 1 then 'Active'
        when status = 2 then 'Inactive'
        else 'Unknown'
end as "Status"
from dim_position

Teradata: How to fetch the latest transaction from a history of transaction?

An example:

select  
 empl_hist.*
from  dim_empl_hist empl_hist
qualify
 1 = row_number() over(partition  by empl_hist.empl_num order by empl_hist.empl_hist_key desc)
where
 empl_status = '1'

Teradata SQL date format

An example:
trans_ts <= cast('2013-02-16' as date format 'yyyy-mm-dd')

SQL UNION vs UNION ALL

In both cases, columns on both sides of the union should have the same datatype.

UNION removes duplicates.
UNION ALL keeps duplicates.

Teradata SQL INDEX function

This is equivalent to ANSI SQL POSITION function.

Example:
SELECT INDEX('Teradata', 'd');    Returns 5  
SELECT INDEX('Teradata', 'er');   Returns 2  
SELECT INDEX('Teradata', 'z');    Returns 0

Teradata SQL Syntax error: All expressions in a derived table must have an explicit name.

The below statement threw this error: SQL Syntax error: All expressions in a derived table must have an explicit name.

select v1.* from dim_y v1
join (
select nk_record_id, count(*)  from dim_y
group by nk_record_id
having count(*) > 1
) v2
on v1.nk_record_id = v2.nk_record_id

Corrected version:

select v1.* from dim_y v1
join (
select nk_record_id, count(*) cnt  from dim_y
group by nk_record_id
having count(*) > 1
) v2
on v1.nk_record_id = v2.nk_record_id

Some Teradata Warehouse Appliance 2700 facts

Ref: http://www.teradata.com/brochures/teradata-data-warehouse-appliance-2700/