Dear All,
I am trying to use TEIID as a media to implement some definition into it. May I know how can I do it?
As attached, I have a viewretailcustomers "view" which I already have the source "DOB" retrieved. However, I would like to create a virtual procedure which I can implement the calculate age function which becomes 1 of the data field "Age" in the viewretailcustomers.
Thank you very much.
Instead of writing a procedue, if you write as UDF then you can inline the usage of the function in the transformation SQL of the "viewretailcustomers". If you want to use the procedure, then call the procedure with "EXEC calcAge" and assign to a CURSOR construct and read the variable out. Check the Reference Guide for the procedure lanaguage.
Ramesh..
Ramesh,
Thank you.
I took your UDF approach and able to get it to work. The following is the steps of my approach. Hope to jot down will help other people who face the same problem.
1) Prepared a Scalar Function under DateUtil.java, prepared a View Model by using the UDF for preparing a column.
2) Compile a jar file and copy it to \server\default\lib
3) Restart server and deploy the new vdb.
Content of DateUtil.java
package hk.plusone.util;
import java.math.BigInteger;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
/**
* @author michaelcywan
*
*/
public class DateUtil {
/**
* Default decimal place
*/
public static final BigInteger DEFAULT_DECIMAL_PLACE = BigInteger.ONE;
/**
* Calculate the years in big decimal and return to caller.
*
* @param p_oldDate
* old date
* @param p_newDate
* new date
* @return BigDecimal( NEW - OLD )
*/
public static final BigInteger calcYears(Date p_oldDate, Date p_newDate) {
return calcYears(p_oldDate, p_newDate, DEFAULT_DECIMAL_PLACE.intValue());
}
/**
* Calculate the years in big decimal and return to caller.
*
* @param p_oldDate
* old date
* @param p_newDate
* new date
* @param p_precision
* precision
* @return BigDecimal( NEW - OLD )
*/
public static final BigInteger calcYears(Date p_oldDate, Date p_newDate, int p_precision) {
BigInteger result = BigInteger.ZERO;
Calendar l_oldDate = getCalendar(p_oldDate.getTime());
Calendar l_newDate = getCalendar(p_newDate.getTime());
// Only require to do calculation if old date < new date
if ( l_oldDate.before(l_newDate) ) {
int years = l_newDate.get(Calendar.YEAR) - l_oldDate.get(Calendar.YEAR);
l_oldDate.roll(Calendar.YEAR, years);
// If the years added to the old date becomes > new date, years - 1
if ( l_oldDate.after( l_newDate) ) {
years--;
}
result = BigInteger.valueOf(years);
}
return result;
}
/**
* @return a Calendar object
*/
public static Calendar getCalendar() {
return getCalendar(System.currentTimeMillis());
}
/**
* @param year
* @param month
* @param date
* @return a Calendar object
*/
public static Calendar getCalendar(int year, int month, int date) {
return getCalendar(year, month, date, 0, 0, 0);
}
/**
* @param year
* @param month
* @param date
* @param hourOfDay
* @param minute
* @return a Calendar object
*/
public static Calendar getCalendar(int year, int month, int date, int hourOfDay, int minute) {
return getCalendar(year, month, date, hourOfDay, minute, 0);
}
/**
* @param year
* @param month
* @param date
* @param hourOfDay
* @param minute
* @param second
* @return a Calendar object
*/
public static Calendar getCalendar(int year, int month, int date, int hourOfDay, int minute, int second) {
Calendar result = GregorianCalendar.getInstance();
result.set(year, month, date, hourOfDay, minute, second);
return result;
}
/**
* @param p_timeInMillis
* time in milliseconds
* @return a Calendar object
*/
public static Calendar getCalendar(long p_timeInMillis) {
Calendar result = GregorianCalendar.getInstance();
result.setTimeInMillis(p_timeInMillis);
return result;
}
}