Easy way to get Syntax for a command in Teradata

A MACRO can help you getting the Syntax help if you are new to Teradata or new to any syntax.

A MACRO is a Teradata extension to ANSI SQL that contains prewritten SQL statements.
The actual text of the macro is stored in a global repository called the Data Dictionary (DD).

A macro allows us to name a set of one or more statements. When we need to execute those statements, simply execute the named macro. Macros provide a convenient shortcut for executing groups of frequently-run SQL statements.

Below is complete list of commands to manipulate macros.


CREATE MACRO macroname AS ( . . . );          Define a macro and store it in the DD.
EXEC macroname;                                             Execute statements within a macro.
SHOW MACRO macroname;                             Display a macro.
REPLACE MACRO macroname AS (. . . );        Apply changes to a macro or create a new one.
DROP MACRO macroname;                              Remove a macro definition from the DD.
EXPLAIN EXEC macroname;                            Display EXPLAIN text for the macro's execution.

Example for Creating Macro, specifically our GETSYNTAX macro that helps us to get online help on td syntax:

getsyntax is a macro that accepts 2 parameters.

·         First parameter is the type of utility that the command belongs to valid values includes the following.

PMPC

BULKLOAD

DUMP

ARCHIVE

MULTILOAD

VERSION

SPL

FASTEXPORT

DATA_TYPE

HELP

SQL

FASTLOAD

TPCCONS

·         Second parameter the command name for which we need to find the syntax.

DEFINE

IMPORT

UPDATE

CREATE TABLE

examples includes:

exec dbc.getsyntax('FASTLOAD','DEFINE');

exec dbc.getsyntax('MULTILOAD','.IMPORT');

exec dbc.getsyntax('SQL','UPDATE');

exec dbc.getsyntax('sql','create table');

Create a macro to generate Online help for Teradata Syntax:

CREATE MACRO DBC.GetSyntax

      (

         TheFunction (CHAR(30)),

         Command  (CHAR(30))

      )

      AS

      (

         SELECT Syntax FROM SysAdmin.HelpSyntax

         WHERE TheFunction = :TheFunction AND

               Command  = :Command

         ORDER BY LineNumber;

      );

To execute the get syntax macro:

exec dbc.getsyntax('sql','create table');

Notice that there is a semicolon before the closing parenthesis. This is a required element of macro syntax.

Use the DROP MACRO command to delete the macro.

   DROP MACRO dbc.getsyntax; 

This command removes the macro from the containing database and also removes its entry from the Data Dictionary.

You can modify a micro by using REPLACE Macro.

The above macro is modified as below with REPLACE Macro command.

REPLACE MACRO DBC.GetSyntax

      (

         TheFunction (CHAR(30)),

         Command  (CHAR(30))

      )

      AS

      (

         SELECT Syntax FROM SysAdmin.HelpSyntax

         WHERE TheFunction = :TheFunction AND

               Command  = :Command

         ORDER BY LineNumber;

      );

Example :

exec dbc.getsyntax('SQL','UPDATE');

	On-Line Help
1
2	[temporal_validtime_qualifier | NONTEMPORAL]
3
4	UPD[ATE] tablename [ [AS] aname ]
5
6	[ FROM tname [as_of_clause][ [AS] aname]
7	[for_portition_of]
8	[... ,tname [as_of_clause][ [AS] aname ] ] ]
9	[for_portition_of]
10
11	{ columnname                                              }
12	SET {                                                         } = expr
13	{ structUDTcolname . attributename [... . attributename ] }
14
15	{ columnname                                              }
16	[ ... , {                                                         } = expr ]
17	{ structUDTcolname . attributename [... . attributename ] }
18
19	[ WHERE condition    ]
20	[                    ] ;
21	[ ALL                ]
22
23	OR  UPSERT Form:
24
25	[validtime_qualifier] UPD[ATE] tablename [ [AS] aname]
26
27	{ columnname                                              }
28	SET {                                                         } = expr
29	{ structUDTcolname . attributename [... . attributename ] }
30
31	{ columnname                                              }
32	[ ... , {                                                         } = expr ]
33	{ structUDTcolname . attributename [... . attributename ] }
34
35	WHERE UPSERT-condition
36	ELSE INS[ERT] [INTO] tablename
37	{ [VALUES] (expr, [ ... ,expr])                              }
38	{ (columnname [ ...,columnname]) VALUES (expr [ ... ,expr])  } ;
39
40	The UPSERT-condition must fully specify the primary index with
41	equality constraint.
42
43	where temporal_validtime_qualifier is any one of the option below,
44	CURRENT VALIDTIME
45	[SEQUENCED] VALIDTIME [<period value expression>]
46	NONSEQUENCED VALIDTIME
47
48	where validtime_qualifier is any one of the option below,
49	CURRENT VALIDTIME
50	[ SEQUENCED ] VALIDTIME [ period of applicability ]
51	NONSEQUENCED VALIDTIME
52
53	where for_portition_of is: FOR PORTION OF derivedpd_column_name FROM date_exp
54	TO   date_exp
55
56	For temporal_qualifier enter help 'temporal qualifier'.
57

                           

Regards,

Pankaj Chahar

Pankajchahar052@gmail.com

+91-8802350184