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

Question

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

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 cannot get the same results by putting it in a filter state. 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 he deleted records A that match record B, where bar = 'foobar'. I don't want this, I want A to be present, but B was zero in this case.

There will be several entries in table B that should be excluded, so I don’t think I can filter this out on the Crystal side without making a lot of mistakes to avoid problems with duplicate entries from table A.

I cannot use the SQL command object, because the third-party application in which we run the reports seems to choke on the SQL command objects.

I can’t use the views, because our support contract does not allow changing the databases, and our supplier is considering adding views to the database modification.

I work with Crystal Reports XI, in particular version 11.0.0.895. In case that matters, I run the Progress 9.1E04 database using the ODBC SQL-92 driver.

Examples of tables and data used in the examples can be created as follows:

 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 reports cannot generate this frequently used SQL statement based on its references and report selection criteria. You must use the "command" or create a view.

In short, crystal sucks.

Answer 2

Is a stored procedure an option for you? If so, you can pre-select the data set this way without resorting to a command, and you can import the stored procedure, like the table.

I would suggest a stored procedure that does select * from b where bar= 'foo' , and joins it, so table b is pre-filtered, so all you have to do is join another join field.

Hope this helps.

Answer 3

Not sure if you can do this in Crystal, but what about joining 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 4

Can you create the appropriate views in the database and base your report on these views? I use Crystal Reports in MSSQL, and often I just create views to avoid such problems.

Answer 5

I see two solutions:

a) accepting the presence of several (unnecessary) lines in B (and repeated values ​​in A), calculating the total values ​​using standard fields and / or formulas is not easy, but almost always possible; b) move B to the subtitle (where you can easily set the filter) and report the necessary values ​​between the main and sub messages using common variables.

Subreports is a powerful tool to solve such problems, unless you need to embed them (impossible) or export reports to excel (adds blank lines, at least in CR 9).

Answer 6

Adding

 (Isnull({b.bar}) OR {b.bar} = "foo") 

in the record selection formula, you should act as you expect.

** edit **

A couple of other things:

• Use a different database driver - your own driver (which avoids ODBC) may act differently. I first noticed this using WITH syntax - the SQL Server ODBC driver did not work, but the native SQL Server driver did.
• While he sacrifices some flexibility, paste the query into the Team, assuming that you can make the third-party product match. Added for completeness.

Answer 7

It seems to me that you do not want to accept any offers, but here is one last shot at him. The solution I used recently when db should remain intact is as follows:

• Set up your Tomcat server so that I can work with JSP and Hibernate.
• Getting Crystal reports for eclipse
• Build a report in Crystal Reports designer with fake data on local db, corresponding to how I will have data in an ideal world.
• Uses the List java servlet transfer for each of the table aliases, so the report data is replaced directly from the POJO. POJOs can, of course, consist entirely of java, pulling content from different db tables and embroidering them as you see fit, often allowing you to provide a completely flattened dataset that Crystal reports are always happy with.

Answer 8

You should not add a filter clause for table b through b.bar is null or b.bar = 'foo' , but you should also not directly access the attributes from table b. You should get all attributes by condition if b.bar = 'foo' according to the formula.