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.
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.
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
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).
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.
CREATE MACRO emp_dept_asof (i_p_date DATE FORMAT 'DD/MM/YYYY')
SELECT * FROM TABLE WHERE DATE_COLUMN=:i_p_date ;
For more details you can go through the below link.