All Forums Extensibility
jhinman732 3 posts Joined 01/08
15 Feb 2012
Can an SQL UDF contain SQL in TD 13.10?

I would like to embed a Select statement in a UDF so that I can pop entries from a queue table into a broader query result.  Something like:

     Create FUNCTION Q_Pop1 (TxnType Char(10))

     RETURNS Integer

     LANGUAGE SQL

     CONTAINS SQL

     SPECIFIC Q_Pop1

     CALLED ON NULL INPUT

     SQL SECURITY DEFINER

     COLLATION INVOKER

     INLINE TYPE 1

     RETURN CASE

          when TxnType = 'TypeA' Then Select and Consume top 1 txn_id from QTBL_1

     END;

The use would be something like:

     Sel A.resource, Q_Pop('Deposit') from A where a.avail = 'Y';

From reading manuals and the forums I'm pretty sure that I cannot embed SQL in a UDF.  I thought about burying the SQL inside of some C but if I get a couple thousand rows in the answer set bouncing out to C to connect back to the database to return a row a couple thousand times wouldn't be efficient... 

Any suggestions?

Thanks,

Jim

Jim Hinman Sr. Consultant - Advanced Analytics Teradata
jhinman732 3 posts Joined 01/08
17 Feb 2012

So, the answer to my question is "No" - a select statement cannot be embedded in an SQL UDF.  SQL "expressions" can be used, but not Select statements.  This capability exists in other DBMS platforms and hopefully will come to Teradata.  In my case I'll have to solve the problem with a more complex stored procedure.  So much for the easy way out!

Jim Hinman
Sr. Consultant - Advanced Analytics
Teradata

You must sign in to leave a comment.