topic Re: UDF to be used in SQL from a Java Class. in SAS Programming

UDF to be used in SQL from a Java Class.

pprashantj — Mon, 10 Oct 2016 16:21:28 GMT

Hi,

I want to create a UDF from a Java class which can be used in a SQL statement.

For eg:

I have an Employee table in DB2 and Oracle.

Columns: EMP_ID, FRST_NM, LST_NM, DEPT_ID

I have a Java calss which concatenates FRST_NM and LST_NM.

package com.udf

public class Concat {
    public String concat(String in1, String in2)
    {
        return in1 + " " + in2;
    }
}

I need to be able to call this concat in any SQL:

Select EMP_ID, concat(FRST_NM, LST_NM) from Employee; -- DB2

Select EMP_ID, concat(FRST_NM, LST_NM) from Employee; -- Oracle

Is this possible?

If yes how would i create a UDF "Concat" from this java class?

Thanks in Advance!!!

Regards,

Prashant.

Re: UDF to be used in SQL from a Java Class.

Reeza — Mon, 10 Oct 2016 22:39:34 GMT

The functionality (concat) you want is easily accessible in SAS.

Using java class functions in SQL on the other hand I'm not sure about. Can you explain a bit more about what you're trying to do? I assume it's make the same code run on both Oracle and SAS without having to make manual changes?

Re: UDF to be used in SQL from a Java Class.

pprashantj — Tue, 11 Oct 2016 13:09:59 GMT

I should have been more clear..The Concat was an example of how we want to use a Java Class.

We have a functionality that we would like to use in SAS and is not provided as a Function in SAS or Database layer.

All the logic has been written in Java and rather than having the logic recreated at each edge point(Oracle, DB2, Teradata, Hive etc)

we would like to create a UDF based on this Java Class.

I am aware we can call Java objects in SAS but have not come across any documentation where we can create a UDF using a Java Class(jar).

Thanks,

Prashant.