StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POIs there a way to make Crystal Reports include a constant in a join condition, without using a SQL command object?
text
Body
copied!<p>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:</p> <pre><code>SELECT a.id, a.other, b.baz FROM a LEFT OUTER JOIN b ON a.id = b.id AND b.bar = 'foo' </code></pre> <p>Expected results:</p> <pre> id other baz -- ---------- ------- 1 Has foo Include 2 Has none (null) 3 Has foobar (null) </pre> <p>I can't get the same results by putting it in the filter condition. If I use the following:</p> <pre><code>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') </code></pre> <p>I get these incorrect results:</p> <pre> id other baz -- -------- ------- 1 Has foo Include 2 Has none (null) </pre> <p>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.</p> <p>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.</p> <p>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.</p> <p>I cannot use views, as our support contract does not permit database modifications, and our vendor considers adding views a database modification.</p> <p>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.</p> <p>The sample tables and data used in the examples can be created with the following:</p> <pre><code>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'); </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload