CODEWITHSUNDEEP
sqlselectteradata
Jan Schebeko
Jan Schebeko

Reputation: 21

SELECT Columns with wildcards?

Is it possible to select columns with wildcard in Teradata?

Like:

Select ABC_* FROM TABLE; -> Results in all columns with ABC names in the beginning.

Upvotes: 2

Views: 2203

Answers (1)

JNevill
JNevill

Reputation: 50034

While I agree with everyone that this is a strange requirement, it's not TOTALLY unheard of. Unfortunately there is no built in SQL way of doing this outside of some scripting.

The basic take away here is that you can't, via SQL, dynamically refer to database objects (tables, views, procedures, macros, functions, databases, columns, etc). Instead you have to write script that generates that SQL and then executes it, and then returns to you the results.

We CAN do all of that directly in our database by using Stored Procedures though.

Here is a quick example that will work in Teradata (after you swap our yourdatabase and yourtable for your actual database and table):

CREATE PROCEDURE return_tablecolumns_with_ABC()

/*
 * We have to tell teradata that this will return
 * a single result set
 */
DYNAMIC RESULT SETS 1
BEGIN   

    /*
     * We need three variables here
     * 1. A varchar to hold our list of columns from dbc.columnsV
     * 2. A varchar to hold the dynamically generated SQL string
     * 3. A cursor to hold the result set of the SQL string
     */
    DECLARE column_list VARCHAR(1000);    
    DECLARE my_sql VARCHAR(500);    
    DECLARE my_cursor CURSOR WITH RETURN ONLY FOR my_statement;

    /*
     * First we query dbc.columsV for a list of columns 
     *  that match your criteria (exists in your table and
     *  and starts with ABC_)
     */
    SELECT TRIM(TRAILING ',' FROM (XMLAGG(trim(ColumnName) || ',' ORDER BY ColumnName) (VARCHAR(1000)))) INTO column_list
    FROM "DBC"."ColumnsV" 
    WHERE DatabaseName = 'yourdatabase' 
        AND TableName = 'yourtable'
        AND columnName LIKE 'ABC_%';

    /* 
     * Now we build our dynamically generated SQL string
     * This could use some polish as it will fail if your
     * columns contain spaces or anything that requires 
     * their names be encapsulated with double quotes...
     */
    SET my_sql = 'Select ' || column_list || ' FROM yourdatabase.yourtable;';

    /*
     * Now we prepare our statement from the dynamically
     * generated SQL string
     */
    PREPARE my_statement FROM my_sql;

    /*
     * And finally we open the Cursor we declared for
     * the statement we just prepared. 
     * We leave the cursor open so it will be returned
     * as a result set when this procedure is called.
     */
    OPEN my_cursor;

END;

You can now call this to get your results:

CALL return_tablecolumns_with_ABC();

Upvotes: 1

Related Questions