FYI -
SET DELETED ON was my problem...arrrggh!

>Well, this seems to be a good one...but..
>If I put this in a remote view, I acutally get a different set of results than if I dont use a view...????
>I have SET ENGINEBEHAVIOR 70
>
>
>
>
>>How about something like????

>>SELECT Cms.order_no, Cms.order_st2, Items.item_id, Items.item_state;
>> FROM ;
>>     cms Cms ;
>>    LEFT OUTER JOIN items Items ;
>>   ON  Cms.order_no = Items.order_no;
>> WHERE  Cms.order_st2 = ( 'PI' ) AND ;
>> 		(items.item_state in ('PI', 'CM'));
>>   AND cms.order_no IN (SELECT cms2.order_no FROM cms cms2,items items2;
>>                        WHERE cms2.order_no=items2.order_no
>>                          AND cms2.order_st2="PI"
>>                          AND items2.item_state in ('PI', 'CM');
>>                        GROUP BY 1 HAVING COUNT(DISTINCT items2.item_state) = 2)
>>

>>
>>
>>
>>>SELECT Cms.order_no, Cms.order_st2, Items.item_id, Items.item_state;
>>> FROM ;
>>> cms Cms ;
>>> LEFT OUTER JOIN items Items ;
>>> ON Cms.order_no = Items.order_no;
>>> WHERE Cms.order_st2 = ( 'PI' ) AND ;
>>> (items.item_state in ('PI', 'CM'))
>>>
>>>
>>>...what my problem is that I want this to show ONLY the records where there is both 'PI' and 'CM' in items.item_state field for a given Order_no. Is it possible to do this in a select statement or am I stuck writing a tiny prg to do it?