Need to select first record only from a left outer join

[nbrown]

I have four tables the first is "Inventory Transactions" this is linked to three other tables via a left outer join.
I need the selling price held on the table "Sales Contract" in order to calculate the Sales value of the inventory transaction. The only common data is the Part Number. My problem is I have potentially several records in the "Sales Contract" table having the same part number. Hence I get potentially several detail lines, since the data is presented in a cross tab layout I can end up with double or treble the sales value (depending on the number of matches in the Sales Contract table).

Is there a way of linking tables so that only the first record matched is used?

Inventory Transaction {Left Outer} Sales Contract
{Left Outer} Item Master
{Left Outer} Item Routing

I multiply Quantity form the inventory transaction with selling price held in the Sales Contract table!

 

[synapsevampire]

You might be able to resolve this by select Database->Select Distinct Records, thought I doubt it.

If you use CR 9, than you can do this directly in the SQL by either using correlated subqueries which use an aggregate function (or TOP 1 if SQL Server), or by creating a derived table which selects top 1 or some max(date) and joining to it.

I would think that the first record isn't necessarily the correct one, generally there is only one active sales contract, and that row should have some indicator of that fact, so adding that to the where clause should suffice - this is all part of a well designed and normalized database.

If not, I would address this in a View or SP.

Since you are using a Cross-tab, it's very difficult to resolve this unless you use more advanced SQL as in CR 9 or in a View.

Let's hear what version of CR and database you're using, etc.

-k

 

[nbrown]

Hi
I'm using CR 8.5 SP1, the data is held on Oracle 8i but is accessable only via a Baan IV driver. The Baan IVc4 system has multiple contract lines since each Part may have several delivery addresses hence potentially several records on the Sales Contract table.

I think it may be worth my while upgrading to CR 9.

Thanks