Hi Kevin, yes, this is similar to the "group concatenation" UDF we have on the Trafodion code examples page <https://cwiki.apache.org/confluence/display/TRAFODION/Trafodion+Code+Examples>. I hope that example will help with what you would like to do. The group concatenation UDF emits only one row per group. In your case, you would need to emit one output row for every input row.
I would recommend to use the PARTITION BY clauses in the example UDF. That can help if you want to have multiple groups, not just one as in your example query. The ORDER BY will help making sure that the id values appear in order. Hans On Sat, Aug 27, 2016 at 12:25 AM, Xu, Kai-Hua (Kevin) <[email protected]> wrote: > Hi Hans, > > > > Thank you for so detailed info timely! Actually, I’m looking for somewhere > to save required column value. Not sure if Trafodion has. > > For example, I’d like to have the following result. Is there any solution? > > Table name: test > > Id name age > > 1 a 11 > > 2 b 11 > > 3 a 11 > > 4 a 11 > > 5 b 11 > > 6 b 11 > > > > Want to have: > > select save(id) from test where name=’b’; > > 2 > > 2,5 > > 2,5,6 > > > > > > Best Regards, > > Kevin Xu > > > > *From:* Hans Zeller [mailto:[email protected]] > *Sent:* Saturday, August 27, 2016 2:43 PM > *To:* [email protected] > *Subject:* Re: [help]How to execute a query in TMUDF > > > > Hi Kevin, > > > > At this time, using the "default" connection is only supported for stored > procedures in Java, not for TMUDFs. I would recommend to just make a > regular JDBC (or ODBC, if you use C++) connection from the UDF. Just make > sure to close the connection, even in cases where an exception is thrown. > > > > Your UDF will run in one or more instances, but certainly not 100 for a > small query. If needed, you can request to run the UDF in a single > instance, through the optimizer interface > <https://cwiki.apache.org/confluence/display/TRAFODION/Tutorial%3A+The+object-oriented+UDF+interface#Tutorial:Theobject-orientedUDFinterface-Optional:Specifythedegreeofparallelism>., > by setting the "function type" of the UDR to GENERIC. For the opposite, to > get parallel execution, set the function type to MAPPER or REDUCER. This > assumes that the UDF carries no state between rows (MAPPER) or partitions > (REDUCERS). > > > > See methods UDRInvocationInfo.setFuncType() > <https://57e4a67d4ac0d80d65fbe17f54e5cf57ab4e0478.googledrive.com/host/0BztdUbYgvYe0N0hSRG9HME85MlE/1.3.0/apidocs/org/trafodion/sql/udr/UDRInvocationInfo.html#setFuncType(org.trafodion.sql.udr.UDRInvocationInfo.FuncType)> > and UDR.describeDesiredDegreeOfParallelism() > <https://57e4a67d4ac0d80d65fbe17f54e5cf57ab4e0478.googledrive.com/host/0BztdUbYgvYe0N0hSRG9HME85MlE/1.3.0/apidocs/org/trafodion/sql/udr/UDR.html#describeDesiredDegreeOfParallelism(org.trafodion.sql.udr.UDRInvocationInfo,%20org.trafodion.sql.udr.UDRPlanInfo)> > . > > > > At runtime you can find out how many instances there are and what instance > the caller is. See methods UDRInvocationInfo.getNumParallelInstances() > <https://57e4a67d4ac0d80d65fbe17f54e5cf57ab4e0478.googledrive.com/host/0BztdUbYgvYe0N0hSRG9HME85MlE/1.3.0/apidocs/org/trafodion/sql/udr/UDRInvocationInfo.html#getNumParallelInstances()> > and UDRInvocationInfo.getMyInstanceNum() > <https://57e4a67d4ac0d80d65fbe17f54e5cf57ab4e0478.googledrive.com/host/0BztdUbYgvYe0N0hSRG9HME85MlE/1.3.0/apidocs/org/trafodion/sql/udr/UDRInvocationInfo.html#getMyInstanceNum()> > . > > > > Let's say you request a single instance. Your methods will still be called > from two processes: One process will invoke the compiler interface(s), the > other will invoke the run time interface processData(). > > > Hans > > > > On Fri, Aug 26, 2016 at 11:16 PM, Xu, Kai-Hua (Kevin) <[email protected]> > wrote: > > Hi all, > > > > Could anybody help? Thanks! > > How to execute a query in TMUDF? Don’t know how to get current connection. > > > > Another question is that “how many TMUDF instance it has in a query”. > Assume there is a query “select udr(a()) from xx”, 100 rows in xx, that > udf will be executed 100 times. How many instance of UDF class will be > created? > > > > Best Regards, > > Kevin Xu > > > > >
