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))
CALLED ON NULL INPUT
SQL SECURITY DEFINER
INLINE TYPE 1
when TxnType = 'TypeA' Then Select and Consume top 1 txn_id from QTBL_1
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...
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!