Exception Join in Crystal Reports

35 pts.
Tags:
Crystal Reports
Crystal Reports 11
Crystal Reports XI
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

SELECT * FROM FILEA A
WHERE NOT EXISTS (SELECT * from FILEB B where A.FIELDX = B.FIELDX)

Discuss This Question: 7  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    The syntax will most likely depend on the database you are using, not on Crystal R. itself.
    68,650 pointsBadges:
    report
  • GregInAtl
    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
    35 pointsBadges:
    report
  • carlosdl
    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.
    68,650 pointsBadges:
    report
  • CharlieBrowne
    The syntax for my example is for an AS400.
    41,370 pointsBadges:
    report
  • GregInAtl
    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.
    35 pointsBadges:
    report
  • carlosdl
    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.
    68,650 pointsBadges:
    report
  • GregInAtl
    [...] 9. CharlieBrowne and Carlosdl helped GreginAtl with an exception join in Crystal Reports. [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following