17.00 - Example: Retrieving Rows from the Data Dictionary and Writing Them to an External Queue on a Client System - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Advanced SQL Engine
Teradata Database
Release Number
September 2020
Content Type
Programming Reference
Publication ID
English (United States)
Last Update

Now consider a somewhat broader use of the UDF defined in Example: Using a UDF to Write a Message to an External Queue. In this example, you retrieve dictionary information from within Vantage and write it to an external MQ queue on a client system.

To do this, you invoke the write_mq UDF for each row found in DBC.Tables that meets the requirements specified in the SELECT request WHERE clause. The SELECT request concatenates the database and table names, creating a VARCHAR string that becomes the input argument to the UDF, which then writes the concatenated databasename.tablename text as a message to the MQ queue.

     SELECT COUNT(*) AS sent_msgs 
   (SELECT write_mq
         Trim(databasename)||'.'||trim(TableName)) AS c1
       FROM DBC.Tables 
        WHERE TableKind = 'T')T;

What this example illustrates is the ease of sending an entire result set of an arbitrary SQL request to a queue that is completely outside Vantage.