Archives of the TeradataForum

Message Posted: Fri, 31 Jan 2004 @ 01:11:37 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi,

One other thing to rememeber about UDFS is that they are DATABASE level objects like, tables and macros.

if you execute

replace function 'replace' ....

( remember the Quotes because it is KEYWORD override )
maybe replace_udf would be a better name for it )

So if you execute

replace function replace_udf ....

you are actually creating

mydb.replace_udf

Therefore it isn't a complete substitute for the REPLACE operator in your SQL because you must specify

mydb.replace_udf ()

every place you reference it or the optimizer won't be able to find it to load and run.

The only way you could get away with specifying

replace_udf()

and have it work correct is if the SQL is executed within the Scope of the database where the function was created.

That means if you have multiple users writing SQL from multiple Logons with different Default databases and using different tables and databases they will probably have to QUALIFY the UDFs to use them.

-- or create the functions in every database that wants to reference them but that seems a bit extreme. This may be doable by creating your own Shared object with all the UDFS defined and then simply executing the REPLACE function pointing at the single shared object. That way you don't actually have to compile every UDF for every user. However I haven't tried that.

Again This brings us back to the fact that

If Teradata was to release its own BUNDLED UDF library it could teach the optimizer to search in that UDF database by default for UDF's so they wouldn't have to be qualified all the time.

Sigh.

	<Prev	Next>		<<First	<Prev	Next>	Last>>