DEAL WITH THE PADDING DOT WHEN CAST A DECIMAL TO CHAR

in Teradata, when cast a decimal to char directly,
the result will show out with a padding dot at the end of the number

eg. the following query will results '123.'
select cast(123. as varchar(10))

the annoying dot can be dealed by cast to Z(I) format

select cast(cast(123. as format 'Z(I)') as varchar(10))

that will bring us the desired results '123'

cast decimal to integer can also do the same thing,
select cast(cast(123. as integer) as varchar(10))



but must take care of the lenth of the decimal number
because the integer can only accommodate numbers from -2,147,483,648 through +2,147,483,647. and when cast to Z(I) the max number can be 9999999999.

Tweet

NULLIF Function

as we know, Teradata provides the well-known method NULLIFZERO.
Teradata also privides us another method named 'NULLIF', that's more powerfull, more flexible,
can be used to replace some query like
CASE WHEN COL1 = VALUE1 THEN NULL ELSE COL1
simply to
NULLIF(COL1, VALUE1)

http://forums.teradata.com/forum/database/coalesce


与广为人知的NULLIFZERO相比，TERADATA提供的NULLIF功能就不那么常用了，其实NULLIF功能更强大，更灵活，某些情况下能被用来简化，替换掉一些CASE语句
如上

Tweet

USE SQL ASSISTANT TO CREATE A TABLE WITH A TIMESTAMP COLUMN BUT THE COLUMN WAS CONVERTED TO CHAR(23)

we found that when we create a table using SQL assistant, and define the column as

HNKO_TIME TIMESTAMP(0)

the create table statement was successd but when we show the created table we found that it was defined in

HNKO_TIME CHAR(23) CHARACTER SET UNICODE NOT CASESPECIFIC,

thanks to the Teradata forums
http://forums.teradata.com/forum/database/cast-string-to-time
http://forums.teradata.com/forum/enterprise/timestamp-interval-not-working-in-batch-jcl-but-works-in-sql-assistant

and now we know that it was because of the  "allow use of odbc extensions" option in sql assistant
unchecked it ,and problem  is resolved

it seems that that if the option is checked, odbc driver will parse the sql query.
any way, the most important thing is , create table ,and never forget to check the definition of the table created . make sure what you created is what you wanted


最近发现，在某台机器上，通过SQL Assistant提交的DDL，如果包含有TIMESTAMP定义的字段，会被置换为CHAR（23），这显然不正确。
罪魁祸首是老旧版本的SQL Assistant中的设置项：allow use of odbc extensions。
如果选中此选项，ODBC驱动看起来会将某些查询解析掉，譬如说DDL中的TIMESTAMP定义。

Tweet

A WAY TO GET ALL ROW COUNT OF ALL TABLES IN A SPECIFIED DATABASE

RUN THE FOLLOWING SQL IN DBC AND THE GENERATED SQL SCRIPT COULD BE USED FOR GETTING ALL TABLES' ROW COUNT


SELECT ' SELECT '' ' || T.TABLENAME || ' '' AS TABLENAME , COUNT(*) AS ROWSNUM FROM ' || TRIM(BOTH FROM DATABASENAME) || '.' || TRIM(BOTH FROM TABLENAME) || ' UNION ' AS X
FROM DBC.TABLES T
WHERE T.TABLEKIND ='T' AND T.DATABASENAME = 'CVDTST'
ORDER BY T. TABLENAME

FROM
https://downloads.teradata.com/forum/analytics/row-counts-for-every-table-in-a-given-database



有时候需要统计某个DB下所有表的行数，在Teradata论坛上看到了这个方法
首先从DBC中取得表名的list，拼装成一个multi statment的SQL查询。
再次执行这个生成的SQL，得到所有表的总行数

Tweet

converting seconds in time format‎

If c1 is always less than 9999 you can use
sel 61 as x,
  cast(cast (x as interval second(4,0)) as interval hour to second(0));

if it's > 9999:
sel 99999 as x,
cast(
     cast (x / 3600 as interval hour(4)) +
     cast (x mod 3600 as interval second(4,0))
as interval day(4) to second(0))

Dieter


http://www.tek-tips.com/viewthread.cfm?qid=505403&page=30

Tweet

Cast Second to HHMMSS

SELECT CAST ((INTERVAL '1000' SECOND) AS INTERVAL HOUR TO SECOND);

Tweet

DATE

DATE '2001-01-01' 

Tweet

Split String by Space

WITH RECURSIVE temp_table (col, layer) AS
(
sel addr_chmi_nm  as col, 1 as layer from batm_jyusho where  index(addr_chmi_nm,' ') = 0
union all
sel substr(addr_chmi_nm, index(addr_chmi_nm,' ')+1) as col, 0 as layer from batm_jyusho
union all
sel trim(substr(addr_chmi_nm, 1, index(addr_chmi_nm,' '))) as col, 1 as layer from batm_jyusho  where  index(addr_chmi_nm,' ') <> 0
union all
sel substr(col, index(col,' ')+1) as col , 0 as layer from temp_table  where layer = 0 and index(col,' ') <> 0
union all
sel trim(substr(col, 1, index(col,' '))) as col , 1 as layer from temp_table  where layer = 0   and index(col,' ') <> 0
)

sel * from temp_table where layer = 1

Tweet

Built-in Functions in Teradata

http://readvitamin.com/2007/08/09/how-to-use-built-in-functions-in-teradata/


Written by admin in Thursday, August 9th 2007 under IT  

First of all what are Built-in functions? These are functions which do not need any sort of arguments but still returns information about the system; user, date, time, session etc. Built-in functions are sometimes referred to as special registers. Mostly used with the SELECT statement. Usage;

SELECT

• ACCOUNT: - If you are wondering about your Teradata Account information just use this function to see your current account string.

• CURRENT_DATE:- Returns the current system date and it is same as the function DATE.

• CURRENT_TIME:- This function returns the current system time and current session ‘Time Zone’ displacement. This function is similar to the TIME function but with extra feature of Time Zone displacement.

• CURRENT_TIMESTAMP:- Returns the current system timestamp (including year, month and day) and current session Time Zone displacement.

• DATABASE: - If you are wondering how to find your current database name, use this function which returns the name of the default database for the current user.

• DATE: - As mentioned earlier, it does return the current date.

• SESSION: - Returns a number for the session the current user is in. Two sessions does not have the same number. If you want to identify your session, this is the function to use.

 • TIME: - As mentioned, this function provides the current time based on a 24-hour day; mean to say for 4:00 pm, you would see 16:00:00.

 • USER: - If you have forgotten your username after you have logged in, this command would come to your rescue. This one gives the user name of the current user.

Tweet

Hash Collision 和 Skew

1.计算每个hash值的记录个数

SELECT                 HASHROW (CHARACTER_COLUMN) 
                        AS "Hash Value"
                        ,COUNT(*)
FROM                DB.TABLE
GROUP BY        1
ORDER BY        2 DESC;

2.计算每个AMP上的记录个数

SELECT                 HASHAMP (HASHBUCKET
                        (HASHROW (CHARACTER_COLUMN))) 
                        AS "AMP #"
                        ,COUNT(*)
FROM                DB.TABLE
GROUP BY        1
ORDER BY        2 DESC; 
3.查找出skew偏移严重的表

SELECT 
 DATABASE,TABLENAME,SUM(CURRENTPERM) AS TableSize,
 MAX(CURRENTPERM) * NUM_OF_AMP  AS VirtualSize,
 100 - AVG(CURRENTPERM)/MAX(CURRENTPERM) * 100 AS TableSkew,
 MAX(CURRENTPERM) * NUM_OF_AMP - SUM(CURRENTPERM) AS SkewSavings
 FROM DBC.TABLESIZE
 WHERE CURRENTPERM > 0
 GROUP BY 1,2,3
 HAVING(SkewSavings > 1000000000 and TableSkew > 10)
 OR TableSkew > 50;

http://blog.chinaunix.net/u3/103331/showart.php?id=2028068

Tweet

TBL01.A LIKE TBL02.B

SELECT DISTINCT
id_int_org_pty,
nm_ext_org_grp_pty
FROM
VMSL_CUST_MAJ
) TBL01
ON
TBL02.id_int_org_pty = TBL01.id_int_org_pty
WHERE
INDEX(TBL02.nm_ext_org_eng,TBL01.nm_ext_org_grp_pty) > 0

或者


SELECT
AB.A
FROM
AB
LEFT OUTER JOIN
BB
ON
AB.A LIKE '%' || B||'%'

Tweet

EXTRACT

EXTRACT
(
YEAR
MONTH
DAY
HOUR
MINUTE
SECOND
TIMEZONE_HOUR
TIMEZONE_MINUTE
FROM value)

ANSI SQL-2003

Tweet

IS-NUMERIC FUNCTIONS IN TERADATA

SELECT * FROM TABLE WHERE TRIM(FIELD) NOT BETWEEN '0' AND '9999999999999999'
OR
(UPPER(TRIM(cd_itm)) (CASESPECIFIC) = LOWER(TRIM(cd_itm)) (CASESPECIFIC))

http://forums.teradata.com/forum/analytics/is-numeric-functions

Tweet

AVG OVER

SELECT
nm_bu,
id_cal_mth ,
AVG(qty) OVER (PARTITION BY nm_bu ORDER BY id_cal_mth ROWS 2 PRECEDING)
FROM
(
SELECT
nm_bu,
id_cal_mth,
SUM(qty_itm) AS qty
FROM
vugd_sal_act_mth
GROUP BY
nm_bu,id_cal_mth
WHERE
nm_bu = 'TV'
) AS TBL
ORDER BY
id_cal_mth

Tweet

CONNECT FIELD BY RECURSIVE TABLE

CREATE MULTISET VOLATILE TABLE VTBL_BASE,
NO FALLBACK ,
CHECKSUM = DEFAULT,
NO LOG
AS(
SELECT
id,
name,
ROW_NUMBER() OVER(PARTITION BY id ORDER BY name) AS row_no
FROM
DWHDEV.TBL_TEST_RECURSIVE
) WITH DATA
PRIMARY INDEX
(id)
ON COMMIT PRESERVE ROWS
;

WITH RECURSIVE temp_table (id, name, row_no, depth) AS
(
SELECT
id,
name,
row_no,
depth
FROM
(SELECT DISTINCT
id,
CAST('' AS VARCHAR(1000)) AS name,
0 AS row_no,
0 AS depth
FROM
VTBL_BASE
)tbl

UNION ALL

SELECT
temp_table.id,
temp_table.name || ' ' || base_table.name,
temp_table.row_no + 1 AS row_no,
temp_table.depth + 1 AS newdepth
FROM
temp_table
INNER JOIN
VTBL_BASE AS base_table
ON
temp_table.id = base_table.id
AND
temp_table.row_no + 1 = base_table.row_no
WHERE
newdepth < 10
)

SELECT
id,
name
FROM
temp_table
WHERE
(id, row_no)
IN
(SELECT
id,
MAX(row_no)
FROM
temp_table
GROUP BY
id
)
ORDER BY
id
;

DROP TABLE VTBL_BASE
;

Tweet

RECURSIVE TABLE

WITH RECURSIVE temp_table (id, name, row_no, depth) AS
(
SELECT
id,
name,
row_no,
depth
FROM
(
SELECT DISTINCT
id,
CAST('' AS VARCHAR(2000)) AS name,
0 AS row_no,
0 AS depth
FROM
TBL_FOR_RECURSIVE_TEST
)tbl
UNION ALL
SELECT
temp_table.id,
temp_table.name || ', ' || base_table.name,
temp_table.row_no + 1 AS row_no,
temp_table.depth + 1 AS newdepth
FROM
temp_table
INNER JOIN
TBL_FOR_RECURSIVE_TEST AS base_table
ON
temp_table.id = base_table.id
AND
temp_table.row_no + 1 = base_table.row_no
WHERE
newdepth < 10
)

SELECT
*
FROM
temp_table
WHERE
(id, row_no) IN (SELECT id,MAX(row_no) FROM temp_table GROUP BY id)
ORDER BY
id

Tweet

CASE

CASE WHEN NM_INT_ORG_PTY = 'TOTAL'

THEN SUM(T_TRAN.QTY_ACT) OVER (PARTITION BY M_MST.ID_CAL)

ELSE T_TRAN.QTY_ACT

END AS QTY_ACT

Tweet

CREATE TABLE FROM TABLE

CREATE TABLE TBL_TST AS (SELECT DISTINCT ID_OGO_FRM_INT_ORG_PTY FROM DWHDEV.WK_QDDGD11010) WITH DATA

Tweet

REPLACE VIEW

REPLACE VIEW View_name AS

LOCKING TABLE Table_name FOR ACCESS

SELECT

field1

,field2

FROM

Table_name

Tweet

HAVING

SELECT Sum(qty),itm_id FROM table_name GROUP BY itm_id HAVING SUM(qty) >= 6000000;

Tweet