Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

user defined functions

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.

Comments

  • PRanaPRana Employee
    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;



Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.