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

hth
heLen