Helen Borrie wrote:

> At 10:46 AM 29/10/2003 -0500, you wrote:
>
>
>>I have been playing with doing the following in a query or SP on
>>serverside for a while now in my spare time, but just cannot seem to get
>>it and was hoping for some help on this one.
>>
>>My customers sometimes complain that duplicate groups of items on
>>kitchen receipts such as the following:
>>
>>HAMBURGER
>> + Mustard
>> + Pickles
>> French Fries
>> Coleslaw
>>
>>HAMBURGER
>> + Mustard
>> + Pickles
>> French Fries
>> Coleslaw
>>
>>HAMBURGER
>> + Mustard
>> + Pickles
>> Baked Potato
>> Coleslaw
>>
>>
>>As you can see, the first two groups are identical while the last group
>>is different so the ideal receipt would look like:
>>
>>
>>HAMBURGER X 2
>> + Mustard
>> + Pickles
>> French Fries
>> Coleslaw
>>
>>HAMBURGER X 1
>> + Mustard
>> + Pickles
>> Baked Potato
>> Coleslaw
>>
>>
>>The relevant fields are:
>>
>>ItemDesc: VarChar
>>ItemID: Based on an Incremented field for the current sale #
>> which is reset with each new sale.
>>ParentID: Integer
>>
>>Now, if you've noticed the top level item of the group is considered the
>> "Parent" item. On the client side, if the item being entered is a
>>Parent Item, then its ParentID field is populated with its own ItemID
>>field because well, its Parent is itself. Each successive "Childs" item
>>is stamped with the ItemID of the Parent Item thus:
>>
>>ItemDesc ItemID ParentID
>>-------- --------- ----------
>>HAMBURGER 1 1
>> + Mustard 2 1
>> + Pickles 3 1
>> Baked Potato 4 1
>> Coleslaw 5 1
>>
>>HAMBURGER 6 6
>> + Mustard 7 6
>> + Pickles 8 6
>> Baked Potato 9 6
>> Coleslaw 10 6
>>
>>HAMBURGER 11 11
>> + Mustard 12 11
>> + Pickles 13 11
>> Baked Potato 14 11
>> Coleslaw 15 11
>>
>>
>>Any help or suggestions would be greatly appreciated as this one has
>>been kicking my butt. ;)
>>--
>
>
> You didn't provide the query statement but it seems as if you are storing
> parents and children in the same table.
>
> Simplest of all would be a selectable SP:
>
> create procedure make_docket (ParentID integer)
> returns (ItemName varchar(n), Qty integer) as
> declare variable vItemID integer;
> begin
> for select Item_Name, order_qty from orders
> where ParentID = :ParentID
> into :ItemName, :Qty, :vItemID do
> begin
> if vItemID <> ParentID then
> Qty = null;
> suspend;
> end
> end
>
> Trying to do it in DSQL:
> You have a self-referencing structure here and you need to treat the parent
> and the children as though they were two tables. A union might work:
>
> select a.item_name, a.order_qty from orders a
> where a.ItemID = a.ParentID
> and a.ParentID = :ParentID
> union
> select b.item_name, null from orders b
> where b.ParentID = :ParentID
> and b.ParentID <> b.ItemID
> order by b.ItemID
>
> I'm suspicious that the null in the b table might be refused. If so, you
> would have to put an integer of some sort in there: 0 or -1 maybe.
>
> If that doesn't work, you can represent this as a master-detail structure
> and tinker with your interface a bit.
> Master set:
> select a.item_name, a.order_qty from orders a
> where a.ItemID = a.ParentID
> and a.ParentID = :ParentID
> Child set:
> select b.item_name from orders b
> where b.ParentID = :ParentID
> and b.ItemID <> b.ParentID
> order by b.ItemID
>

Thanks for responding Helen. I will give these suggestions a try and
yes, the structure is selferencing as mentioned above. I've been
picking at this one for a few weeks now.... LOL.

--

Warm Regards,

Lee