Anyone have an example how to run sql query as text (dynamic 'select * from **bleep**' as text)?

Database
Visitor

Anyone have an example how to run sql query as text (dynamic 'select * from **bleep**' as text)?

I haven't been able to find a basic example of how to take a queries as text (in a volatile table) and run each query, inserting the singke row result into another volatile table.

 

I'm trying to build a basic process to loop through all columns of a table and run a SQL statement upon each column (then union the results into a output or table). A basic data quality process to count the number of nulls etc for each column. The result of each query is the same structure, and only 1 row.

 

So far I have a successful process that builds the sql statements as text/string and inserts them into a volatile table (I use dbc.columns). 

Each row in the volatile table contains a sql statment as text/string (ie. 'SELECT col_1 FROM my_table',  then 'SELECT col_2 FROM my_table' etc). 

I can manually copy and paste the text/string sql statement, and it runs successfully... but I don't want any manual steps, and need to run each text/string sql statement.

 

So, suppose I have a volatile table with three rows of data, each row contains a string query like this;

 

SELECT 
  CAST('Age' AS VARCHAR(100)) AS null_column 
, SUM(CASE WHEN Age IS NULL THEN 1.0 ELSE 0.0 END) AS null_cnt 
FROM customers
;

 

I have not been able to figure out how to subsequently run each row as a query (and display or insert the results into another volatile table).

 

I've tried using EXECUTE IMMEDIATE or dbc.SysExecSQL  in a sql statement, but i get a bunch or errors including " SET MY_QUERY_TEXT Failed. 5882:  Invalid SET statement in the triggered action."  and also "CALL Failed. 5495:  Stored Procedure  'dbc.SysExecSQL' does not exist.". 

 

SQL Assistant only.  I'm probably limited to running sql statements (no stored procedures or UDF's etc) because of security restrictions/limits imposed by the Teradata DBA.

 

Any tips or suggestions?

 

Thanks!

 

Tim

1 REPLY
Senior Apprentice

Re: Anyone have an example how to run sql query as text (dynamic 'select * from **bleep**' as text)?

Hi Tim,

 

The 'standard' way of doing this is using a stored procedure, but if your DBA won't let you (why not?) then you might be able to...

 

SQLA has a RUN command. The syntax is:

RUN filename

I'm not sure how completely automated you can make this process.

You've already built the SQL to be executed.

Export that to a plan text file.

Use the RUN command to read in that plain text file (which can contain multiple sql requests);

 

The SQLA documentation says that the RUN command must be the only command in the query window, so you might have some manual intervention.

 

Must this be done using SQLA? How about using BTEQ? That would be a lot easier and can be 'automated' by simply running a BTEQ script.

 

HTH

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com