Skip to Content
author's profile photo Former Member
Former Member

CR equivalent to SQL "IN" keyword

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.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Jan 04, 2008 at 02:54 PM

    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')

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 10, 2008 at 06:53 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.