Example: SELECT Statements With Scalar Subqueries in Expressions and as Arguments to Built-In Functions - Advanced SQL Engine - Teradata Database

You can specify a scalar subquery in the same way that you specify a column or constant in an expression or as an argument to a system function. You can specify an expression that is composed of a scalar subquery wherever an expression is allowed in a DML statement.

Following are examples of the types of expressions that you can code using scalar subqueries.

Arithmetic expressions

     SELECT (fix_cost + (SELECT SUM(part_cost)
                         FROM parts)) AS total_cost, …

String expressions

     SELECT (SELECT prod_name
             FROM prod_table AS p
             WHERE p.pno = s.pno) || store_no …

CASE expressions

     SELECT CASE WHEN (SELECT count(*)
                       FROM inventory
                       WHERE inventory.pno = orders.pno) > 0
                 THEN 1 
                 ELSE 0
            END, …

Aggregate expressions

     SELECT SUM(SELECT count(*)
                FROM sales
                WHERE sales.txn_no = receipts.txn_no), …

Value list expressions

     … WHERE txn_no IN (1,2, (SELECT MAX(txn_no)
                              FROM sales
                              WHERE sale_date = CURRENT_DATE));