16.10 - Names Subject to Object Naming Rules - Teradata Database

Teradata Database SQL Fundamentals

Product
Teradata Database
Release Number
16.10
Release Date
June 2017
Content Type
Programming Reference
Publication ID
B035-1141-161K
Language
English (United States)
Name Example Usage
ACCOUNT (acc)
CREATE USER u1 as perm=10e6, password = pass1,
account=’acc2’, default journal table=u1jnl;
MODIFY USER u1 AS ACCOUNT=’acc1’;
BEGIN QUERY LOGGING WITH SQL ON ALL ACCOUNT =’acc1’;
CREATE PROFILE prof5 AS PERM=10e5 PASSWORD=pass5 ACCOUNT= ‘acc3’;
ATTRIBUTE (att)
CREATE TYPE udt2 AS (att1 INT, att2 DATE) NOT FINAL;
ALTER TYPE udt2 ADD ATTRIBUTE att3 FLOAT;
AUTHORIZATION (auth)
CREATE AUTHORIZATION db1.auth1 as INVOKER USER 'bdUsr' PASSWORD 'bdPsswd';
COLUMN (col)
CREATE FUNCTION fn1 (NumRows INTEGER)
RETURNS TABLE
(col1  INTEGER,
  col2  INTEGER)
LANGUAGE C
NO SQL
PARAMETER STYLE SQL
EXTERNAL NAME ‘SS!easy!easy.c’;
CREATE TABLE t1 (col1  int);
CONSTRAINT (con)
CREATE TABLE t2 (col1 int CONSTRAINT  con2   CHECK (i=1));
CREATE TABLE t3 (col3 int CONSTRAINT  con3   REFERENCES t1(c1));
ALTER TABLE t1 ADD CONSTRAINT  con1  UNIQUE (c1);
CONSTRAINT (con)

(row level security constraint)

CREATE CONSTRAINT  conA  data_type,[NULL|NOT NULL],
VALUES value_name:integer_code ...
[, value_name:integer_code],
Insert SYSLIB.insert_udf_name ,
Update SYSLIB.update_udf_name ,
Delete SYSLIB.delete_udf_name ,
Select SYSLIB.select_udf_name ;
DATABASE (db)
CREATE DATABASE  db1  as perm=10e6;
FUNCTION (fn)
CREATE FUNCTION  fn2  (integer, float)
RETURNS FLOAT
LANGUAGE C
NO SQL
EXTERNAL NAME   ‘sp:/Teradata/tdbs_udf/usr/second1.so’;
RENAME FUNCTION fn1 AS  fn2;
ALTER SPECIFIC FUNCTION  fn1  EXECUTE PROTECTED;
CREATE CAST (udt1 as udt2) WITH FUNCTION sysudtlib.fn1(udt1);
CREATE ORDERING FOR udt1
ORDER FULL BY MAP WITH FUNCTION SYSUDTLIB.fn3;
CREATE TRANSFORM FOR abov_strInt
abov_strInt_IO (TO SQL WITH SPECIFIC FUNCTION SYSUDTLIB.abov_StrIntToSQL,
FROM SQL WITH SPECIFIC FUNCTION SYSUDTLIB.fn4);
GLOP Set (glop)
CREATE GLOP SET db.glop1;
INDEX (idx)
CREATE INDEX  idx2  (c1) on t1;
COLLECT STAT USING SAMPLE ON db1.idx1 
COLUMN col1 FROM db2.t1;
CREATE TABLE t1 (col1 int, col2 int)
INDEX  idx1  (c2);
COLLECT STAT INDEX  idx1  ON tab1;
MACRO
CREATE MACRO m1 AS (SELECT ‘abc’; );
RENAME MACRO m1 to m2;
METHOD (mth)
CREATE CONSTRUCTOR METHOD  mth1 
(P1 INTEGER)
FOR abov_strInt
EXTERNAL NAME 'SS!udt_strintcons!udt_strintcons.c!F!strintcons';
CREATE TYPE udt1 AS (P1 INTEGER)
NOT FINAL CONSTRUCTOR METHOD  mth2  (P1 INTEGER)
RETURNS udt1
SELF AS RESULT
LANGUAGE C
DETERMINISTIC
NO SQL;
ALTER SPECIFIC METHOD  mth2  FOR UDT_name EXECUTE NOT PROTECTED;
PARAMETER (pm)
CREATE MACRO m1 (pm1  int) AS (SEL :pm1; );
CREATE FUNCTION Find_Text (pm1  VARCHAR (500), pm2  VARCHAR (500))
RETURNS CHAR
LANGUAGE C
NO SQL
PARAMETER STYLE TD_GENERAL
EXTERNAL NAME
'SS:pattern2:/home/i18n/ca3/v2r5/UDF/tests/SrcNI/pattern2/pattern2.c:SI:pattern2:/home/i18n/ca3/v2r5/UDF/tests/SrcNI/pattern2/pattern2.h:SL:curses';
CREATE PROCEDURE xsp_cr003(IN  pm1  varchar(20), OUT  pm2  VARCHAR(20))
LANGUAGE C
PARAMETER STYLE SQL
EXTERNAL NAME 'CS!xsp_cr003!xsp_cr003.c';
CREATE TYPE abov_strlnt AS (pm2  INTEGER)
NOT FINAL CONSTRUCTOR METHOD  abov_strlnt  (P1 INTEGER)
RETURNS  abov_strlnt 
SELF AS RESULT
LANGUAGE C
DETERMINISTIC
NO SQL;
REPLACE PROCEDURE sp3(out  pm1  integer)
BEGIN
  DECLARE var1 INTEGER DEFAULT 10;
  SET p1 = var1;
END;
PASSWORD (pass)
CREATE USER u1 as perm=10e6,
password =  pass1  , account=’acc1’;
MODIFY USER u1 AS PASSWORD=pass1;
PLAN_DIRECTIVE (pd)
INSERT PLAN_DIRECTIVE  pd1  IN  pd1cat 
(sel   'pd_dip002_TestID1_1_pd',
      t100k_b.i1, pd_db1.t100k_b.i2, pd_db1.t100k_b.i5
from  t100k_b, pd_db1.t200_a
where
      t100k_b.i2 = pd_db1.t200_a.i2
;
)
WITH '1: NESTED JOIN(DUPED( SCAN( PD_DB1.T100K_B)),'
'INDEXED WITH NO ROWIDLIST( INDEX(I2) PD_DB1.T200_A))'
COMMENT 'PD for pd_dip002_TestID1_1_pd'
PROCEDURE (sp)
CREATE PROCEDURE  sp2  ( a varchar(20), OUT result1 VARCHAR(20))
LANGUAGE C
PARAMETER STYLE SQL
EXTERNAL NAME 'CS!xsp_cr003!xsp_cr003.c';
RENAME PROCEDURE  sp2  TO sp3;
ALTER PROCEDURE  sp3  LANGUAGE C COMPILE ONLY;
PROFILE (prof)
CREATE PROFILE prof5 AS
   PERM=10e5
   PASSWORD=pass5
   ACCOUNT= ‘acc5’;
PROXYUSER (pxyuser)

PROXYROLE (pxyrole)

SET QUERY_BAND='PROXYUSER=pxyuser1;
PROXYROLE=pxyrole1;' FOR SESSION;
BEGIN TRANSACTION;
SET QUERY_BAND='PROXYROLE=pxyrole2;' FOR TRANSACTION;
SELECT *
FROM table1;
The reserved query band names PROXYUSER and PROXYROLE are not permitted in the profile query band.
QUERY (qry)
WITH qry1(a) AS (SELECT col1 FROM tab1)
SELECT a FROM qry1;
ROLE (role)
CREATE ROLE role1;
EXTERNAL ROLE (extrole)
CREATE EXTERNAL ROLE extrole2;
TABLE (t)
CREATE TABLE  t1  (col1 int);
CREATE ERROR TABLE  t2  FOR t1;
RENAME TABLE t1 AS  t3;
MODIFY USER u1 AS DEFAULT JOURNAL TABLE=t1;
CREATE TRIGGER trig5 AFTER UPDATE ON t3
REFERENCING OLD_TABLE AS t1 NEW_TABLE AS  t2FOR EACH Statement
  When (35 <= (sel X.price from t2 X INNER JOIN t1 Y ON
                         X.pubyear= Y.pubyear))
(
  INSERT t4 SELECT t2.titles, t2.price FROM t2;
);
BEGIN LOADING db1.t3
ERRORFILES db1.t1, db1.t2
INSERT INTO db1.t3.*;
BEGIN DELETE MLOAD TABLES db1.t1
WITH db1.t2
ERRORTABLES db1.lt3;
BEGIN IMPORT MLOAD TABLES db1.t1
WORKTABLES db1.t2
ERRORTABLES db1.t3 db1.t4 ;
CREATE USER u1 as perm=10e6,
password = pass1, default journal table=t1;
TRANSFORM_GROUP (tg)
CREATE TRANSFORM FOR abov_strInt 
tg1  (TO SQL WITH SPECIFIC FUNCTION
SYSUDTLIB.abov_StrIntToSQL,
FROM SQL WITH SPECIFIC FUNCTION
SYSUDTLIB.abov_StrIntFromSQL);
DROP TRANSFORM tg2 FOR arsv_point_t ;
TRIGGER (trig)
CREATE TRIGGER trig1 AFTER  UPDATE OF (i)
ON t1 REFERENCING OLD AS OLDROW
NEW AS NEWROW FOR EACH ROW
WHEN (i > 10)
(INSERT INTO t1log VALUES (OLDROW.i, NEWROW.i); );
RENAME TRIGGER trig1 TO trig2 ;
UDT (udt)
CREATE TYPE  udt15  AS VARCHAR(15) FINAL;
CREATE CAST (udt1  as  udt2) WITH FUNCTION
sysudtlib.abov_DistFloattoDistInt(udt1)
CREATE ORDERING FOR  udt3 
ORDER FULL BY MAP WITH FUNCTION
SYSUDTLIB.abov_StrIntOrdering;
CREATE TRANSFORM FOR  udt3 
abov_strInt_IO (TO SQL WITH SPECIFIC
FUNCTION SYSUDTLIB.abov_StrIntToSQL,
FROM SQL WITH SPECIFIC FUNCTION
SYSUDTLIB.abov_StrIntFromSQL);
DROP TRANSFORM transform_io FOR  udt4  ;
USER_NAME (u)
CREATE USER  u1  as perm=10e6,
password =  pass1, account=’u1account’ ;
USING VARIABLE NAME (usingvar)
.import data file = c:\temp\fil1.data
using (usingvar1  int,  usingvar2  char(10))
insert db1.tab1(:usingvar1, :usingvar2);
VIEW (v)
CREATE VIEW v1 AS SELECT ‘abc’ col1 ;
REPLACE VIEW v1 AS SELECT ‘abc’ col1 ;
RENAME VIEW v1 AS v2 ;