help with table udf

Extensibility

help with table udf

I REALLY need to be able to define a table function that returns the results of a query.

 

Our metrics are recorded at the employee level according to their hr_emp_no.  We have a emp_dept table that tracks the employee's employment departments over time..

 

hr_emp_no, dept_id, from_date, to_date

 

I want to make a table function that the team can call like

select * from emp_dept_asof('01/01/2017')

to associate their metrics with the correct department dimensions for the correct point in time.

 

I've read that table udfs have to be written in Java or C; but, every example I see is pulling some external data in whereas I want to query the teradata database.

 

Any feedback is greatly appreciated.

 

--Hise

4 REPLIES
Junior Contributor

Re: help with table udf

For me this sounds like a Temporal Table or a Macro or a Stored Procedure, but not like a Table UDF.

Re: help with table udf

Around here they do not manage slow changing dimensions with new database generated ids when the atributes of the employee or attributes of the department change... instead they use the hr emp_nbr in all the related tables..  The employee table can return the emp_nbr multiple times, each row representing the various state of employee attributes.   I need the ability to get the appropriate "state" of the the employee and join that to any other table... not a good fit for a stored procedure or temp table.

 

select

from employee_asof('12/31/2016') emp

join employee_dept_asof('12/31/2016) empdept on emp.emp_nbr = empdept.emp_nbr

join dept_asof('12/31/2016') dept on empdept.dept_id = dept.dept_id

Teradata Employee

Re: help with table udf

UDFs do not receive a connection to the database. In theory, you could create a Table Operator to apply a filter predicate and pass it the entire dimension table as an argument; but that does not seem a reasonable solution.

 

This appears to be a good use case for temporal (Valid-Time).

Highlighted

Re: help with table udf

Currently in JAVA UDF sqls are not allowed.

 

You can create a macro which can return a result set.

You can pas the date as i/p.

and then execute the macro.

Example :-

 

 CREATE MACRO emp_dept_asof (i_p_date DATE FORMAT 'DD/MM/YYYY')
 AS (

SELECT * FROM TABLE WHERE DATE_COLUMN=:i_p_date ;

);

 For more details you can go through the below link.

https://info.teradata.com/HTMLPubs/DB_TTU_16_00/index.html#page/SQL_Reference/B035-1144-160K/oug1472...