user defined functions

Carlos_Gaguance_1

Hi...

I have a question, I hope somebody can help me.

I need to create a UDF which can execute some select statement  and compare the result, but I don't know how create select statement into UDF. 

The UDF is in JAVA language.


Please help me.


Thanks.

PRana

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

Carlos_Gaguance_1

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;