Hi Ming,
That is an interesting UDF.
The syntax you have shown should work. Please see the modified example
below from regression test udr/TEST001.
I think either we have a bug or it is an issue with delmited names. You
could try these two steps to understand the issue better.
a) select * from udf(solrUDF('db','iphone')) u where u."id <http://u.id/>" =
1; OR
b) cqd UDR_DEBUG_FLAGS '64'; select * from udf(solrUDF('db','iphone')) u ;
Approach b) will print out the output column names which could be helpful.
Debug flags are documented by Hans at <goog_1873561815>
https://cwiki.apache.org/confluence/display/TRAFODION/Tutorial%3A+The+object-oriented+UDF+interface
Thanks
Suresh
Example from udr/test001 showing that syntax given above should work. The
bug I mentioned could be due to the fact that solrUDF does not have a table
input. We have had issues with 0 table valued input UDFs in the past.
SELECT *
FROM UDF(sessionize_dynamic(TABLE(SELECT userid,
JULIANTIMESTAMP(ts) as TS,
ipAddr
FROM clicks
PARTITION BY ipaddr ORDER BY ts),
'IPADDR',
cast('TS' as char(2)),
60000000)) u
where u.session_id < 10;
SESSION_ID SEQUENCE_NO USERID
TS IPADDR
-------------------- --------------------
-------------------------------- -------------------- ---------------
1 1 super-user
212331578399500000 12.345.567.345
2 1 super-user
212331585599500000 12.345.567.345
2 2 super-services
212331585599500000 12.345.567.345
2 3 super-services
212331585599550000 12.345.567.345
--- 4 row(s) selected.
>>
Notice how column names are in upper case in the output.
On Wed, Jun 1, 2016 at 4:18 AM, Liu, Ming (Ming) <[email protected]> wrote:
> Hi, all,
>
>
>
> I wrote a simple TMUDF which will perform a solr query and get result as a
> table value.
>
>
>
> It can work like this:
>
>
>
> >>select * from udf(solrUDF('db','iphone'));
>
> id description
>
> ------ ----------------------------------------
>
> 1 iphone 5
>
> 2 iphone 5s
>
> --- 2 row(s) selected.
>
>
>
>
>
> As you can see, it will return two columns: ‘id’ and ‘description’. Now I
> want to do a filter on id, so I try this:
>
>
>
> >>select * from udf(solrUDF('db','iphone')) u where u.id = 1;
>
>
>
> It failed and report this error:
>
>
>
> *** ERROR[4003] Column U.ID is not a column in table U, or, after a
> NATURAL JOIN or JOIN USING, is no longer allowed to be specified with a
> table correlation name.
>
>
>
> *** ERROR[8822] The statement was not prepared.
>
>
>
> Because I want to join the udf result from the source Trafodion table, so
> I have to reference the columns in the UDF.
>
>
>
> Please help, how can I reference to the column returned from a UDF?
>
>
>
> Thanks,
>
> Ming
>
>
>
