crystal-reports - 有没有办法让 Crystal Reports 在连接条件中包含一个常量，而不使用 SQL 命令对象？

Question

我想要做的是一个表的外部连接，我根据匹配常量从连接表中排除记录，但保留主表中的记录。例如:

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

预期成绩:

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

我得到这些不正确的结果:

    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

Crystal 报表无法根据其链接和报表选择标准生成常用的 SQL 语句。您必须使用“命令”或构建 View 。

简而言之， Crystal 很烂。