Vertica is not supporting UDF that calls a table within it . Can anyone help me finding an alternate to this . Most of out reporting queries calls business functions . This is a showstopper for us .
@BikashNayak Could you please share sample example on what you are trying... what is the error you are receiving?
sample Oracle function example below. I need to create the same in vertica .
CREATE OR REPLACE FUNCTION REPORT_NET.GET_WORKCENTER_ID
FROM WC1.WORKCENTER WC
WHERE WC.WORKCENTER_NAME =V_WORKCENTER_NAME;
Vertica CREATE FUNCTION doesn't support SELECT currently. Some options:
Current Vertica versions support OUT and INOUT for stored procedures, so you may write a stored procedure that executes the function and returns the result.
You could also write a Python scalar function that executes a SQL query and returns the value (or NULL), though this requires a SQL connection and login credential in the UDSF.
Business Functions at the reporting level doesn't scale very well with increasing volume.
With Vertica you want to implement the business logic either in a view or better while inserting into a table.