Multi-table joins have the following requirements:

• Only two tables may be joined at a time
• Each Join must have an associated
• On clause placement must associate the two tables being joined

 

Example

 Select information.customer_id

          customer_name,

          custom_adress

from customer_information information right outer join

campaign_new_year campaign

on   information.customer_id = campaign.customer_id

left outer join job j

on campaign.customer_id = j.customer_id

 

The placement on the ON clause in the select is important. The rules are:

• The first On clause (from left to right) is evaluated first
• AN On clause applies to immediately preceding operations

 

When defining 3 or more tables in outer join operations you must consider the results of each separated join operation as a derived or temporary table

 

Example:  

 

Select * from

  (

(Select information.customer_id

          customer_name,

          custom_adress

from customer_information information right outer join

campaign_new_year campaign

on  information.customer_id = campaign.customer_id) result

left outer join job j

on result.customer_id = j.customer_id) a

 

 

• Null represents absence of data value of a column in a row
• Null is a SQL keyword
• Null is neither a data type no characteristic of data
• When doing is ascending sorting Null is before numeric negative values and before blank character values
• To prohibit nulls columns have to be defined as Not Null
• Null on arithmetic operations always produces Null result
• It is possible to use Null and not Null in select statement

Example

 

Select customer_id, address

From   basic_data

Where address is not null

The Like operator implements pattern matching for strings of character data.

The operator requires two parameters: a string expression to be searched and a string pattern to search for.

The string pattern may contain specific characters, as well as the following wildcards:

 

%= zero or more character positions

_= one character position

 

String pattern examples:

 

LIKE ‘IRE%’ – begins with ‘IRE’

LIKE ‘%IRE%’ - contains ‘IRE’ anywhere

Like ‘_IRE’ – contains IRE in the last three positions

LIKE ‘%I_’ – contains I in the next to last position

 

Example

 

Select first_name, last_name

From   customers

Where last_name like ALL (‘%K%’,’%J%’)

Quantifiers:

 

ANY = any single condition must be met (or)

SOME = same as any
ALL = all conditions must be met (and)

 

LIKE with ESCAPE character.

 

The ESCAPE feature of LIKE permits wildcard characters to be treated as non-wildcards.

Characters following the escape character are not treated as wildcards.

 

Example

 

Select campaign_name

From   customers

Where campaign_name like ‘_H_%’  escape ‘H’

 

Campaign_name

A_new_year

B_new_client

A_saving_account_805

 

    
A database may contain :

Tables – contain data
Views – are previously defined “windows” to the data 
Macros – are stored SQL statements
Triggers – are SQL statements associated with a table
Join indexes – are user defined pre-joins or pre-aggregations
Stored-procedures – are pre-written SQL scripts with defined procedural processing using Stored procedures control statements

 
Definition of  default database:

.logon irena
Password: abc123
The normal default database for this logon is irena until:
    * User logs off
    * A new database control is used
 
The database command used to change default database
You can easily verify you current default database:

Select database
Other build-in function:

Current_date
Current_time
Current_timestamp
Date
Profile
Role
Time
 
Bteq – is a front end tool for submitting SQL queries. Bteq stands for basic Teradata Query programs
There are many tools available to provide help for the Teradata Sql user or programmer

    *         Help command
Provides information on a specified database object.
There are many objects for which help may be requested.

Databases and users

HELP DATABASE
HELP USER

Database Objects

HELP TABLE – shows the name, data type and comment of all columns
HELP VIEW
HELP MACRO
HELP COLUMN
HELP INDEX
HELP STATISTICS
HELP CONSTRAINT
HELP VOLATILE TABLE
HELP JOIN INDEX
HELP HESH INDEX
HELP TRIGGER
HELP PROCEDURE
HELP FUNCTION

    *            Show command
 
SHOW TABLE – shows the most current definition of the table
SHOW VIEW
SHOW JOINT INDEX
SHOW PROCEDURE
Shoes how an object was created, intended as a convenience to the developer

    *         Explain feature

 Shoes how a query will execute in Teradata
It’s recommended to use Explain modifier before the SQL request – to see the plan execution of SQL. Explain function returns the plan how the optimizer will execute the SQL.
For example if you see that you request cause Full-table scan on a big table or cause Cartesian product join without intention – it’s preferable to rewrite request and write more efficient joint.

Explain provides:

    *          Translation of AMP steps
    *          A row count and time estimation for comparison only
    *          Which indexes if any will be used
    *          Whether individual steps will be used in parallel

Purpose:

CREATE MACRO defines a set of statements that are frequently used or that perform a complex operation. The statements in the macro body are submitted when the macro is invoked by a subsequent EXECUTE statement.
REPLACE MACRO redefines an existing macro. If the specified macro does not exist, REPLACE MACRO creates a new macro with that name.