Nick Upson wrote:

>
>>I have 2 tables, SalesOrder (SO) and salesorderline (SOL). Given a SO
>>reference (unique key to SO table) I need to locate any other Sales orders
>>with the same combination of orderlines regardless of quantity
>
>
> missed a bit: I need to do this when I save a SO with it's SOL and not do the
> save if I find a matching order that already exists. So the SOL records don't
> exist at the time I need to make the check unless I save it all and
> rollback/delete if I find one afterwards which is not ideal due to SO
> generator values
>

I guess I don't completely understand. What I think I understand is
that there's a 1:n relationship between SO and SOL. You want to find
existing SO records that have the same number of SOL records and
matching contents in all SOL records on fields other than quantity.

select count (*) from SO x where
(my_count = select count (*) from SOL y
where (y.id = x.id))
and exists (select 1 from SOL a
where a.item = my_itema
and a.size = my_sizea ...)
and exists (select 1 from SOL b
where z.item = my_itemb
and z.size = my_sizeb ...)

....


my_count is the number of SOL rows you're thinking about storing.
my_itema is the item name of the first SOL row your thinking about storing.
my_sizea is some other attribute you care about from the SOL row
... are the other attributes you care about.
and so on.

Regards,


Ann