How to mimic 'NOT EXISTS' in Crystal Reports XI R2 1

[SundancerKid]

I have a Report that I want to mimic the NOT EXISTS clause in Crystal Reports XI R2.

I have a Report that uses Business View Manager linked to Oracle 10g database.

I am only using the Main Survey Table Only in my Crystal Report.

If the ProductID Parameter is ...ALL then I want all of the main records selected.

Else

If the ProductID's are selected and they exist in the One to Many Table then the associated main record is valid else do not print.

Main Survey Table (One to One) and SurveyProduct (One to Many) Table both have SurveyID as their common field.

Any Suggestions?

 

[IanWaterman]

Create a parameter and check allow multiple values

Just add filter to select expert,

{ProductID} = {?Param}

Ian

 

[SundancerKid]

Hi Ian,

Thanks for the reply.

So I will have to do a Left Outer Join from my Main Table Survey (One-One) to SurveyProduct (One_Many) table right?

(I am changing a report that already exists and it was only using the Survey Table)

What is happening is I have been rewriting a report that worked fine when it was referenced in a Stored Procedure and it only referenced the Survey Table.

But to get the NOT EXIST clause to work I have to jump thru hoops in Crystal.

They do not like the crappy Prompt screens coming from the Stored Procedure.

 

[IanWaterman]

NOT Exists is couter intuitive to this statement

If the ProductID's are selected and they exist in the One to Many Table then the associated main record is valid else do not print.

This read like an EXISTS

Thus an equal join will return valid records.

If you want records which do not exist in SurveyProduct
then do a left outer, and add to select statement

and isnull(SurveyProduct.ProducyID)

Ian

 

[SundancerKid]

Problem Solved

What I did was I to the Original Oracle View and added the SurveyProduct to the Select statement as a Left Outer Join and then I added the ProductID to the Select.

In Crystal instead of using a detail line I am using a Group on SurveyID so that I get unique SurveyID's.

Since ProductID is now part of the same master record I am able to include or exclude based upon ProductID.

Select Statement used:

(IF '...ALL' in (? ProductID) then
True
Else
IF Q.ProductID = (? ProductID) then
True
Else
False)