Is there a way to make Crystal Reports include a constant in a join condition, without using a SQL command object?

Question

What I want to do is an outer join to a table, where I exclude records from the joined table based on matching a constant, however keep records from the main table. For example:

SELECT a.id, a.other, b.baz
FROM a
LEFT OUTER JOIN b
  ON a.id  = b.id
  AND b.bar = 'foo'

Expected results:

    id  other       baz      
    --  ----------  -------  
    1   Has foo     Include  
    2   Has none    (null)   
    3   Has foobar  (null)   

I can't get the same results by putting it in the filter condition. If I use the following:

SELECT a.id, a.other, b.baz
FROM a
LEFT OUTER JOIN b
  ON a.id  = b.id
WHERE (b.bar IS NULL OR b.bar = 'foo')

I get these incorrect results:

    id  other     baz      
    --  --------  -------  
    1   Has foo   Include  
    2   Has none  (null)   

Where it excluded records of A that happen to match a record of B where bar = 'foobar'. I don't want that, I want A to be present, but B to be nulls in that case.

Table B will have multiple records that need excluding, so I don't think I can filter this on the Crystal side without doing a lot of messing around to avoid problems from duplicate records from table A.

I cannot use a SQL command object, as the third party application that we are running the reports from seems to choke on SQL command objects.

I cannot use views, as our support contract does not permit database modifications, and our vendor considers adding views a database modification.

I am working with Crystal Reports XI, specifically version 11.0.0.895. In case it makes a difference, I am running against a Progress 9.1E04 database using the SQL-92 ODBC driver.

The sample tables and data used in the examples can be created with the following:

CREATE TABLE a (id INTEGER, other VARCHAR(32));
CREATE TABLE b (id INTEGER, bar VARCHAR(32), baz VARCHAR(32));
insert into A (id, other) values ('1', 'Has foo');
insert into A (id, other) values ('2', 'Has none');
insert into A (id, other) values ('3', 'Has foobar');
insert into B (id, bar, baz) values ('1', 'foo', 'Include');
insert into B (id, bar, baz) values ('1', 'foobar', 'Exclude');
insert into B (id, bar, baz) values ('1', 'another', 'Exclude');
insert into B (id, bar, baz) values ('1', 'More', 'Exclude');
insert into B (id, bar, baz) values ('3', 'foobar', 'Exclude');

Answer 1

Not sure if you can do this in Crystal but how about joining to a Select?

SELECT a.id, x.baz
FROM a
LEFT OUTER JOIN 
 (SELECT id, baz FROM b WHERE bar = 'foo') As x ON a.id  = x.id

Answer 2

Can't you create appropriate views in database and base your report on these views? I'm using Crystal Reports on MSSQL and often I just create views to avoid similar problems.

Answer 3

Sometimes you don't have the ability to create views on the back end (I'm using an ODBC connection to an offsite hosted Intersystem Cache db), DJ's suggestion did work for me in a similar situation though.

Thanks DJ

Answer 4

I can see two solutions:

a) accept presence of multiple (unneeded) rows in B (and repeated values in A), calculate totals using runnign total fields and/or formulas - not easy way, but almost always possible;
b) move B into subreport (where you can set filter easily) and communicate needed values between main and subreport using shared variables.

Subreports are powerful tool for solving this kind of problems, unless you need to nest them (not possible) or export reports into excel (adds empty lines, at least in CR 9).

Answer 5

Nah, Shitful reports can't generate that commonly used SQL statement based on it's (links and report selection criteria). You have to use a "command" or build a view. In short, Crystal sucks.