17.10 - Example: Matching Patterns from Another Table - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Functions, Expressions, and Predicates

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1145-171K
Language
English (United States)

The following form of subquery might return none, one, or several values:

expr [ NOT ] LIKE quantifier (subquery)

The following example shows how you can match using patterns selected from another table.

There are two base tables.

This table … Defines these things …
Project
  • Unique project ID
  • Project description
Department_Proj The association between project ID patterns and departments.

Department_Proj has two columns: Proj_pattern and Department. The rows in this table look like the following.

Proj_pattern Department
AP% Finance
AR% Finance
Nut% R&D
Screw% R&D

The following query uses LIKE to match patterns selected from the Department_Proj table to select all rows in the Project table that have a Proj_Id that matches project patterns associated with the Finance department as defined in the Department_Proj table.

   SELECT * 
   FROM Project
   WHERE Proj_Id LIKE ANY
    (SELECT Proj_Pattern 
     FROM Department_Proj
     WHERE Department = 'Finance');

When this syntax is used, the subquery must select the same number of expressions as are in the expression list:

( expr [,...] ) [ NOT ] LIKE quantifier (subquery)

For example:

   (x,y) LIKE ALL (SELECT a,b FROM c)

is equivalent to:

   (x LIKE c.a) AND (y LIKE c.b)