CR equivalent to SQL "IN" keyword

[Former Member]

Post Author: mcatd

CA Forum: Formula

I have a set of XML-generated data, arranged in the XML equivalent of tables. One "table," BASE, shows all bus routes that originate at a certain base. Another "table," DRIVERROUTES, shows all bus routes a given driver is authorized to drive.

What I want is all the routes at a certain base that the given driver is NOT authorized to drive. If I could use SQL, then I could simply query for all routes that are in BASE but not in DRIVERROUTES. But I can't use SQL. The querying part all happens upstream from me, the report writer.

I've tried to approach this with joins, but haven't had any luck so far. If I put in a <> join, then I get either a) a gigantic set of every possible combination of non-matching records, or b) a message telling me I can't meddle with an existing join.

How, in Crystal, can I compare the contents of two "tables" and select based on the comparison? Crystal's "IN" keyword wants me to actually type out an array rather than passing, say, a table name.

I'm using Crystal 10.

[Former Member]

Post Author: mcatd

CA Forum: Formula

I'm sorry; I'm not understanding this very well. Am I supposed to create tables out of the XML data that's given to me? Because that's not something I know how to do.

There isn't any base.driver in the data as it comes to me. The BASE table has base.base and base.route. The DRIVERROUTES table has driverroutes.driver and driverrroutes.route (as well as some other fields that aren't relevant to this particular problem). If I suppress the rows where base.route <> driverroutes.route then I get a huge set of every possible combination excepting those ones where base.route matches driverroute.route.

What I want is every route that appears in BASE without appearing in DRIVERROUTES for a given driverroutes.driver.

[Former Member]

Post Author: bettername

CA Forum: Formula

I mocked your problem up using SQL rather than xml files, but this method worked for me... I created the two tables (see bottom for code snippet), that gave Driver 1 access to routes A,B and C, and Driver 2 access to routes X, Y and Z.

Add both 'tables' to the Database expert, but remove any linking between them. Crystal will give you a message saying that this isn't supported, but ignore it.

Add your fields to the report (I placed base.driver, driverroutes.driver and drriverroutes.route) and run it - you'll see what essentially is a cross-join between the two tables (every combination of records).

Now, conditionally suppress records where base.driver = driverroutes.driver and you'll be left with a list of driverroutes.route that don't 'belong' to drivers.driver

ie:

Drivers.Driver Route1 X1 Y1 Z2 A2 B2 C

Code Snippet:

create table base (driver int)insert into base values (1)insert into base values (2)

create table driverroutes (driver int, route varchar(10))insert into driverroutes values (1,'A')insert into driverroutes values (1,'B')insert into driverroutes values (1,'C')insert into driverroutes values (2,'X')insert into driverroutes values (2,'Y')insert into driverroutes values (2,'Z')