At 03:02 AM 28/03/2006, you wrote:

>Hi all,
>
>
>I have a customers table, where customers can have any number of
>classifications (customer classes). This is done by having the classes in
>another table, and a connector table CustToClass with fields custid and
>classid. To get a list of all customers belonging to given classes, I would
>do something like:
>
>SELECT id, name FROM customer WHERE id IN
> (SELECT custid FROM custtoclass WHERE classid IN (1, 2, 3))
>
>Performance issues aside (though let me have it if this is all nuts, I'm
>willing to learn ;-), this works.
>
>Now I have a window in an app where I need to set a filter based on the
>abovementioned fashion. I suppose I could do it also by creating the query
>anew instead of making such a funny filter, but my question is, should it
>work as a filter? Because with IBO 4.6A it doesn't - the filter string is
>parsed somewhere, and becomes as follows:
>
>id IN (SELECT, custid, FROM, custtoclass, WHERE, classid, IN, (, 1, 2, 3))
>
>which obviously fails. So, a little help: is this IBO being too clever, or
>me? If it's me, then I'd appreciate suggestions how to do it right.

I think it should work as a filter, although you WOULD need to have
correct correlation syntax for *any* multi-table queries (which you
don't). You are starting with a completely unqualified main query
and then trying to apply a correlated subquery to it. Logically, it
can't work. What the parser seems to be doing here is treating each
"word" that appears after the keyword "IN(" as a an item in the list
because it can't work out what it's supposed to be "INning".

If you must do this, make sure that the entire distribution is fully
qualified first:

Main query:
SELECT
Customer.id, Customer.name FROM customer

Filter would have to resolve as:
[WHERE] Customer.id IN
(SELECT custtoclass.custid FROM custtoclass
WHERE custtoclass.classid IN (1, 2, 3))

If that still causes problems for the parser because of the subquery,
you might get it to work if you ensure that the entire filter string
is enclosed in brackets as well, i.e. so the filter string looks like this:

'(Customer.id IN
(SELECT custtoclass.custid FROM custtoclass
WHERE custtoclass.classid IN (1, 2, 3)))'

Helen