2014-08-27
Teradata - Table inventory query
select
DatabaseName
,
TableName
,
TableKind
from
dbc.tablesX as T
where
T.AccessCount
is not null
and T.DatabaseName in (
' DATABASE_1_NAME_HERE
'
, 'DATABASE_2_NAME_HERE'
)
;
Teradata - PeriodID arithmetics (PeriodID = date in YYYYMM format)
--declare prmPeriodID as integer;
--set prmPeriodID = 201112;
sel
-- PeriodID,
starting point, main
201112
as PeriodID
-- Year
,
cast( 201112 / 100 as integer) as PeriodYear
-- Month
,
201112 - ( cast( 201112 / 100 as integer) * 100 ) as PeriodMonth
-- Date
,
cast
(
cast( 201112 / 100 as integer)
||
'-'
||
trim( 201112 - ( cast( 201112 / 100 as integer) * 100 ) )
||
'-01'
as date--varchar(50)
)
as PeriodDate
-- Previous Month
,
cast
(
cast( 201112 / 100 as integer)
||
'-'
||
trim( 201112 - ( cast( 201112 / 100 as integer) * 100 ) )
||
'-01'
as date--varchar(50)
)
- interval '1' month
as PeriodDateMinus1Month
-- Next Month
,
cast
(
cast( 201112 / 100 as integer)
||
'-'
||
trim( 201112 - ( cast( 201112 / 100 as integer) * 100 ) )
||
'-01'
as date--varchar(50)
)
+ interval '1' month
as PeriodDatePlus1Month
-- NExt PeriodID
,
(
-- Year
(
extract(
year from
cast
(
cast( 201112 / 100 as integer)
||
'-'
||
trim( 201112 - ( cast( 201112 / 100 as integer) * 100 ) )
||
'-01'
as date--varchar(50)
)
+ interval '1' month
)
* 100
)
-- Month
+
extract (
month from
cast
(
cast( 201112 / 100 as integer)
||
'-'
||
trim( 201112 - ( cast( 201112 / 100 as integer) * 100 ) )
||
'-01'
as date--varchar(50)
)
+ interval '1' month
)
)
as PeriodIDPlus1
;
Teradata - Macro example/template
/*
--
Execution
exec
CFDW2_RDL_ERIDL_CFS_VWS.mPeriodTest(201112,12);
*/
replace macro
CFDW2_RDL_ERIDL_CFS_VWS.mPeriodTest
(
prmPeriodID
INTEGER
,
prmAdd BYTEINT
)
as
(
-- Calculations
--declare
prmPeriodID INTEGER DEFAULT 201112;
--set prmPeriodID =
( ( ( :prmPeriodYear - 1 ) * 100 ) + 12 );
select
:prmPeriodID
as PeriodID
,
:prmAdd as Adder
;
);
Teradata - Database, table and columns inventory query
SELECT
T.DatabaseName
,
T.TableName
,
T.TableKind
,
C.*
FROM
dbc.tablesX T
inner join
dbc.columnsX C
on
T.DatabaseName
= C.Databasename
and T.TableName = C.Tablename
where
1=1
-- Access count
--T.AccessCount is
not null
-- Filter in/out
databases you don't want to inspect
/*and
T.DatabaseName NOT in (
''
)*/
-- Request text?
--and T.RequestText
LIKE '%TERM_HERE%'
-- Filter for
column names of interest
--and C.ColumnName like '%PARTIAL_COLUMN_NAME_HERE%'
ORDER BY
T.DatabaseName
,
T.TableName
,
C.ColumnName
;
Teradata - Basic arithmetics sampler
select
2+3
,
2-3
,
2*3
,
2/3
,
2/3.0
,
2.0/3.0
,
2.00/3.00
,
cast( 2 as float ) / cast( 3 as float )
,
2**3
,
2||3
,
EXP(1)
,
LOG(1)
,
2 / NULLIFZERO(0)
,
RANDOM (1,100)
,
SQRT(9)
,
2 mod 3
,
3 mod 2
,
date '2012-06-06' - date '2012-06-01'
,
cast('' as float ) as EmptyStringToFloat
;
Teradata - Function dates demo
select
-- Date pieces
current_date as Today
,
current_timestamp as aTimestamp
,
current_date (format 'yyyyddd') (char(7)) as Formatyyyydd
,
extract( year from date '2012-06-06' ) as TheYear
,
extract( month from date '2012-06-06' ) as TheMonth
,
extract( day from date '2012-06-06' ) as TheDay
-- Period ID
,
cast(
(
extract( year from current_date ) * 100 )
+
extract( month from current_date )
as int
)
as PeriodID
-- Alternative
,
CAST( cast((current_date (format 'YYYYMM')) as varchar(6)) AS INT ) as PeriodIDAlt
-- Day ID
,
cast(
(
extract( year from current_date ) * 10000 )
+
( extract( month from current_date ) * 100 )
+
extract( day from current_date )
as int
)
as DayID
-- Timestamp
conversions
,
cast(
cast(CURRENT_DATE as timestamp(3))
+
((CURRENT_TIME - time '00:00:00') hour to second(0))
as timestamp(3)
)
as Timestamp3
,
current_date (format 'yyyyddd') (char(7)) as JulianDate
-- Date artithmetic
,
current_date + 1 as Tomorrow
,
add_months( current_date, 1) as NextMonth
--, current_date +
interval '1' month as NextMonth2
,
add_months( current_date, -1) as PreviousMonth
--, current_date -
interval '1' month as PreviousMonth2
,
current_date - date '2012-01-31' as DateDiffInDays
,
cast(
cast(
cast( current_date - date '2012-01-31' as float)
/
cast(30.5 as float)
as decimal(4,0)
)
as byteint
)
as DateDiffInMonths
;
Teradata - Environmental variables
select
account
,
current_date
,
current_time
,
current_timestamp
,
database
,
date
,
session
,
time
,
user
;
Subscribe to:
Posts (Atom)