user defined functions

Comments

• Options

  PRana Employee

  February 2014

  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
  
  
  
  

  0
• Options

  Carlos_Gaguance_1

  February 2014

  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;
  
  
  
  

  0