Home > IT Answers > Business Intelligence > Exception Join in Crystal Reports
GregInAtl
35 pts.
 Exception Join in Crystal Reports
Crystal Reports XI, Crystal Reports, Crystal Reports 11, DB2 on AS400
How do I create an expection join in Crystal Reports 11. That is, I want ONLY the rows in FILEA that do not have a row in FILEB when joining them on FIELDX
ASKED: Jul 11, 2011  1:48 PM GMT
UPDATED: March 31, 2012  9:45:52 PM GMT
RELATED QUESTIONS
CharlieBrowne
24,725 pts.
Answer Wiki:
SELECT * FROM FILEA A
WHERE NOT EXISTS (SELECT * from FILEB B where A.FIELDX = B.FIELDX)
Last Wiki Answer Submitted:  Jul 11, 2011  2:29 PM (GMT)  by  CharlieBrowne   24,725 pts.
To see other answers submitted to the Answer Wiki View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



 

The syntax will most likely depend on the database you are using, not on Crystal R. itself.

Carlosdl
 60,245 pts.
 

The database is DB2 on the AS400 and it does support exception joins. When you are using the SQL editor on the AS400, the syntax is as folows:
SELECT * FROM FILEA EXCEPTION JOIN FILEB on FIELDX.FILEA = FIELDX.FILEB

So, my question is: how to do the same thing in Crystal Reports 11

GregInAtl
 35 pts.
 

GregInAtl, maybe I’m missing something.

A CR report usually gets data from a database, and the syntax used in the report’s SQL command must be supported by the database being used. So, if the database used by the report supports the exception join syntax, then you can use it in CR.

Another option would be the query posted by CharlieBrowne, although I would avoid selecting all columns (SELECT *) in the subquery.

Carlosdl
 60,245 pts.
 

The syntax for my example is for an AS400.

CharlieBrowne
 24,725 pts.
 

I am not really after the SQL syntax to do an exception query, I know what that is. What I want to know is where do you specify this in CR. CR has some drag and drop capabilities to link files. I believe this is done under the links tab in Database Wizard.

I have since found that you can specify your SQL using the add command option when creating a connection under database expert. Maybe thee is another way.

GregInAtl
 35 pts.
 

Oh, now I understand.

AFAIK, by using the database expert (wizard) you can only create the standard inner and outer (left, right, full) joins. So, if I’m correct, your only option would be adding a command as you mentioned in your last paragraph, where you can enter the desired SQL command.

Carlosdl
 60,245 pts.
 

[...] 9. CharlieBrowne and Carlosdl helped GreginAtl with an exception join in Crystal Reports. [...]

The Most-Watched IT Answers: July 26, 2011 - ITKE Community Blog
 0 pts.