exclude items in a table 2

[JRine0101]

I'm running Crystal 11 over a BOXI universe. I want to report on Items NOT bought by a customer. I have a query of available_items and a query of items_bought by the customer. I want to list all available_items that are NOT in the items_bought query. What is the correct link between the two queries?

 

[beanbrain]

I'm not familiar with BOXI, however, the code I might use would look like;

select available_items from itemsTablename where available_items not in (select items_bought from itemsBoughtTablename)

Not knowing what your table names are I've substituted itemsTablename for availabe and itemsBoughtTablename for purchased items table.

 

[davidwendelken]

Crystal Reports 10 connecting to Oracle via the supplied Oracle driver:

I tried adding a NOT IN to a filter formula and it would not accept it.

I haven't tried a MINUS operator yet, I'm curious as to why you didn't recommend that.

 

[beanbrain]

David:

I'm sorry I wasn't clear about where to use this. The statement above is a SQL statement. Since JRine0101 was talking about queries I supplied a SQL code segment.

I haven't tried NOT with IN though I cannot imagine why it would not work. Could you please supply the code you are talking about?

 

[beanbrain]

I guess I need to expand my horizons a bit. NOT and IN do not work together. Trying to come up with a workaround.

 

[beanbrain]

OK...got it.

Try something like this;

not ('NY' in {table.mailingLabel})

where 'NY' is what you want to exclude while looking in the fieldname {table.mailingLabel}.

 

[davidwendelken]

I don't have access to Reports until the morning, but the sql I'm used to allows things like:

some_column_name not in ('abc','def','ghi')

This would filter out any record that had any of the three values in the column.

I don't see how your approach would work there, but I can't test possibilities until the morning.

 

[lbass]

In a CR record selection formula, you would use:

not({table.field} in ["abc","def","ghi"])

Single quotes work fine, too.

-LB

 

[davidwendelken]

Thanks! Worked like a charm.

I didn't realize that I had to use square brackets instead of parentheses around the list of values. Nothing I tried would ever have worked until I would have found that in a manual.

Can't wait to find out what other SQL syntax differences there are!

 

[beanbrain]

DavidWendelken:

Actually LBass' last example and my last example are both Crystal syntax. My first example, in this thread, is SQL syntax.