How to query Multivalued fields

[ganesan]

Hi,

I am created the table with many columns and one of the column is Multivalued field.
My Query: select * from tablename where multivaluedfieldname.value="zzz"

I got the error : net.ucanaccess.jdbc.UcanaccessSQLException: user lacks privilege or object not found: MULTIVALUEDFIELDNAME.VALUE

Please guide me to work well.

Thanks in advance.

Regards,
Ganesan G

[Marco Amadei]

See here and here my answers on stackoverflow within the 2.0.9 relase note:"Allowed filtering on complex type columns(version, attachment, multi-value).
You can do that through one of the following functions: Equals, EqualsIgnoreOrder, Contains."
Finally see the related example at the bottom of the tab "Getting started" of the ucanaccess website.

[ganesan]

Hi Marco,

Thanks for your reply.

How can i perform joins for complex type.
Eg : SELECT table1.events table2.employee
FROM table1 LEFT JOIN table2 ON table1.events.value = table2.employee;

table1.events is an multivalued field
tabl2.employee is an shorttext field.

Please suggest me an answer. Equals function expect the arrays.

Thanks,
Ganesan G

[Marco Amadei]


ps=conn.prepareStatement("select * from TABLE1 where contains(MULTI_VALUE_DATA,?) ");
ps.setObject(1, SingleValue.multipleValue("aaaaaaa14"));

Notice that in your example the join condition would be based on an function(not indexable), and it would lead to a full scan.