user defined functions

Hi Carlos,

I'm not sure what your question is. Do you want to execute a select statement from within the UDF?  You can execute queries in a UDF using JDBC. Can you please elaborate a bit more on what you want to establish.

Please note that the UDF itself is executed in a select statement.
SELECT UDF(x,y) FROM foo;


Pratibha

Ok. I need convert this oracle function to vertica function, but I don't know how connect to vertica from JAVA, I proved with JDBC but i can't connect. 

FUNCTION FUNCION1 (fecha date,  nombre varchar2, codigo integer)
   RETURN NUMBER
IS
     li_valor_devuelto   NUMBER;
     li_valor_devueltob   NUMBER;
     li_valor_devueltoc   NUMBER;
BEGIN
IF codigo =53 THEN

     select SUM(a.valor) into li_valor_devuelto from (
SELECT SUM (fac.fisb_val_monetario)/1000 AS valor
            FROM dim_entidad ent,
            fact_indicadores_sbs fac,
            dim_indicador_sbs ind,
            dim_tiempo tie
      WHERE     fac.fisb_isb_sk = ind.isb_sk
            AND fac.fisb_ent_sk = ent.ent_sk
            AND fac.fisb_tie_sk = tie.tie_sk
            AND ind.isb_sk IN (52,56,54)
            AND tie.tie_fecha = fecha
            AND ent.ent_peergroup like  nombre||'%'
   GROUP BY ent.ent_peergroup, tie.tie_fecha, ind.isb_indicador_l
      )a;

RETURN li_valor_devuelto;

ELSIF codigo=85 THEN

     select SUM(a.valor) into li_valor_devuelto from (
SELECT SUM (fac.fisb_val_monetario)/1000 AS valor
            FROM dim_entidad ent,
            fact_indicadores_sbs fac,
            dim_indicador_sbs ind,
            dim_tiempo tie
      WHERE     fac.fisb_isb_sk = ind.isb_sk
            AND fac.fisb_ent_sk = ent.ent_sk
            AND fac.fisb_tie_sk = tie.tie_sk
            AND ind.isb_sk = 85
            AND tie.tie_fecha = fecha
            AND ent.ent_peergroup like  nombre||'%'
   GROUP BY ent.ent_peergroup, tie.tie_fecha, ind.isb_indicador_l
      )a;
      
      select SUM(a.valor) into li_valor_devueltob from (
SELECT SUM (fac.fisb_val_monetario)/1000 AS valor
            FROM dim_entidad ent,
            fact_indicadores_sbs fac,
            dim_indicador_sbs ind,
            dim_tiempo tie
      WHERE     fac.fisb_isb_sk = ind.isb_sk
            AND fac.fisb_ent_sk = ent.ent_sk
            AND fac.fisb_tie_sk = tie.tie_sk
            AND ind.isb_sk =89
            AND tie.tie_fecha = fecha
            AND ent.ent_peergroup like  nombre||'%'
   GROUP BY ent.ent_peergroup, tie.tie_fecha, ind.isb_indicador_l
      )a;

RETURN li_valor_devuelto-li_valor_devueltob;

END IF;

END FUNCION1;