Can we access a table from SQL UDF in TD 14?

[mani_atkuri]

Hello,

We are trying to create a SQL Udf .It should access the table. For example, like below

CREATE FUNCTION SYSLIB.INCLUDE (RLS CHAR(1))

RETURNS VARCHAR(90)

LANGUAGE SQL

CONTAINS SQL

DETERMINISTIC

COLLATION INVOKER

INLINE TYPE 1

RETURN

 SELECT SECUR_LBL FROM  db.SECUR_LABEL_USER WHERE USER_ID=USER ANd SECUR_TYPE=RLS ;

Is this possible with Td 14 version? Any help would be really helpful.

Thank you.

[Adeel_Chaudhry]

Are you converting fuctions from other RDBM (e.g SQL Server) to Teradata?

What will be the use of this fuction?

Regards, Adeel

[ToddAWalter]

A SQL UDF is only allowed to perform an expression and return the result. A Select is not allowed in a SQL UDF.

[jilseman]

accessing objects from within functions has been a useful feature in other DBMS (eg SQL Server)

you are able to build a reusable set of functions for lookups, data validations, etc that can be use in other SPs, querys, etc allowing the developers to join to functions as virtual tables, select only certain values from the output, etc.

While Teradata is superior is many areas, it seems they are about 5-6 years behind other DBMSs when is comes to being developer friendly.

Are there plans to add this capabilty?

[dheld2]

So if that is the case, (

A SQL UDF is only allowed to perform an expression and return the result. A Select is not allowed in a SQL UDF.

)

THen what is the correct way to impliment what Mani has asked?  Can you select and return data in a c or Java Function UDF?

[Adeel_Chaudhry]

For the correct way o implement that .... again .... What will be the use of this fuction?

[jaskaran]

If we cannot access other table from a udf in terdata, Can we access a properties file from inside a udf so that we can fetch configuration or create a lookup?

[GJColeman]

Yes, but in that case you should make sure the UDF is running as PROTECTED mode.  (This is the mode it has when first created or replaced; it only gets to not-protected by an explicit ALTER command.)

[GJColeman]

I have been prompted to point out that I/O should only be done by table functions or table operators.  If this is a simple scalar UDF, you should use GLOP instead of an actual file for properties - that is exactly what GLOP was made for.  For details on how to use it, see the SQL External Routine Programingming manual at info.teradata.com

[jaskaran]

Can we use cufconfig utility for passing variables to C++ udf.  I created a properties file and added it to the cufconfig UdfEnvFile variable . However i was not able access the configuration from the properties file.

I looked out for examples , but i didn't got any lead on that. Can you please elaborate it with an example ?