EXISTS capability?

[mgrejtak]

I'm using Crystal 8.5 against an Oracle DB and am wondering what the best way is to accomplish the following is

Pull inventory for items that either

a) have a schedule against them
b) have messages against them (MRP)

then show the schedules and/or MRP messages

a) and b) are independent of each other.

I can use subreports to generate the info once I know I have the right items - but how to determine which items I need to start with?

I'm presuming either an EXISTS clause or something of the like would handle this?

 

[Turkbear]

Hi,
Same Table?

If so, then use an OR clause..

If not, join the tables ( Left Outer between Inventory and the other(s)) should enable you to do what you want..

Perhaps some samples ( Table structures, Linkable fields, desired output) would help..

Also, you may want to pre-create the data needed using a View or Stored Proc in the database..That would be much more efficient than using a sub-report.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[mgrejtak]

The question posed was against three different data sets which can all be joined by part number and cost center.

Main Data Set: Inventory
1st Join Table: Schedule
2nd Join Table: Messages

If there is a record in either join table (or both) then the inventory record should be shown. However, if there not a record in either join table, then the inventory record should not be shown.

An outer join presumes you want to see inventory data regardless. We would only like to see inventory data when either one of the joins can be met.

Hopefully that clarifies what we're trying to do.

 

[kskid]

With my 8.5 reports, I use Database->Show SQL Query and insert my EXITS statement at the end.

It may work in SQL expression but haven't played with it so I don't now for sure. Perhaps one of the guru's can shed some light.

-LW

 

[Turkbear]

Hi,
You can use the Outer Join anyway ( since Inventory links to 2 tables and you want data from it if even one of them has matching data, but you cannot determine in advance which one) , then set up a supression formula for the Details that will supress displaying the data if BOTH schedule and messages fields are null.

Using a View or Stored Proc is still is the better choice..





To Paraphrase:"The Help you get is proportional to the Help you give.."