Thanks Helen for the detailed answer, please see my comments below.

>
> Select
> CAST(' ' || PT.Description as Char(31)) as DESCRIPTION
> , PT.WIDTH
> , PT.HEIGHT
> , Count( QTY_ORDER ) as POCKET_COUNT
> , CAST(null as INTEGER) as TOTAL_ROWS
> , CAST(NULL AS INTEGER) AS EXTRA_POCKETS
> from ORDERITEMS OI
> join PACKET P on P.ITEMNUM = OI.ITEMNUM and not P.ISHARDWARE = 1
> Join PACKET_TYPE PT on PT.IDNUM = P.PKT_GROUP
> where OI.INVOICENUM = :INVOICENUM
> Group by PT.Description, PT.WIDTH, PT.HEIGHT
> Having Count( QTY_ORDER ) <> 0
> UNION
> Select
> CAST('PACKET WIDTH ' || PT.WIDTH || 'mm' as Char(31) )
> , PT.WIDTH
> , PT.HEIGHT
> , COUNT( QTY_ORDER ) AS POCKET_COUNT
> , CAST(NULL as INTEGER) AS TOTAL_ROWS
> , CAST(NULL AS INTEGER) AS EXTRA_POCKETS
> from ORDERITEMS OI
> join PACKET P ON P.ITEMNUM = OI.ITEMNUM and not P.ISHARDWARE = 1
> JOIN PACKET_TYPE PT ON PT.IDNUM = P.PKT_GROUP
> WHERE OI.INVOICENUM = :INVOICENUM
> GROUP BY PT.WIDTH, PT.HEIGHT
> HAVING COUNT(QTY_ORDER) <> 0
> ORDER BY 2, 1
>
> Problems here:
>
> 1. The set has no output column that corresponds to the

masterlink. You

> would need to include INVOICENUM in both subsets and in the GROUP

BY spec.
I had thought about this, but it does perform as expected without
the INVOICENUM column, so I have omitted it.

>
> 2. However, this doesn't totally solve the problem if you need to

perform

> DML based on the values in the set, since you don't have anything

from

> which to make keylinks. For this query (though not generically

for union

> sets) the PK of the OrderItems table into the set would be needed

in the

> spec and in the GROUP BY as well (for both subsets), in order to

provide a

> data access path back to the underlying record.
>

I don't need any DML, so all of the update/insert/delete concerns
are gone.

> 3. The clause "and not P.ISHARDWARE = 1" is not a join

criterion. It

> belongs in the WHERE clause and it should be "and P.ISHARDWARE =

0"

> (assuming this is a 2-state Boolean).

Yes, it is a two state Boolean, however it put it here thinking it
would narrow down the number of records for the total join to
consider before it got to the "Where" clause. I guess thats not the
case?

>
> 4. It is pointless including these columns in the set:
>
> CAST(NULL as INTEGER) AS TOTAL_ROWS
> , CAST(NULL AS INTEGER) AS EXTRA_POCKETS
>
> since there is no way that they can be of any use in the client

(except

> maybe as "spacers" in a grid!!)

I have these here to do some calculations based on outside criteria
provided by the user. I do some calculations in an OnDrawCell event
and display the results in these columns. So they are indeed spacers
for a grid but they do have a specific purpose. Is there some other
way to make a "calculated field" with user provided information?

I guess I could do a SP and provide the INVOICENUM and other params
that the user controls at runtime, but I'm not sure if that has any
advantages over the OnDrawCell event.

>
> 5. Even without the complication of the union, a grouped query

can never

> be an updatable detail set since, again, the set cannot provide a

data

> access path to a single record on the server.
>
> 6. And note that, when creating the view, the ORDER BY clause

must be

> omitted. It can, of course, be used in the SQL of the ib_query.
>
> However, I'd also want to review whether a union is really what

you need

> for this set. Even with all the amendments suggest, the set would

still

> get you two records for each record in the underlying table. If

that's the

> purpose, OK, I guess.
>
> If the actual intention is just to deliver the derived field, then

you

> don't need the union. The expression "CAST('PACKET WIDTH ' ||

PT.WIDTH ||

> 'mm' as Char(31) )" can be output as a derived field in a single-

path query.

>
> Helen
>

The union gives me a count of different groups of seed packets based
on their size (width and height). It is used to count display
pockets in the layout of a rack used to display the goods where all
the flowers would be in one range of locations (i.e. contiguous
rows), veg in another etc. The OnDrawCell event gives me the number
of packets in a row (for that size packet) and how many extra
packets outside an even number of rows.

In the first row below six 85mm wide Flowers will fit in a row, so
this is three complete rows plus two extra packets. Unfortunately I
don't know the width of the rows that might be used since there are
so many rack arrangements, so I just use the user input to calculate
the last two columns at runtime.

Here is the output I get from this union (including the OnDrawCell
event results):

DESCRIPTION, WIDTH, HEIGHT, POCKET_COUNT, TOTAL_ROWS, EXTRA_POCKETS

FLOWER 85 110 20 3 2
VEGETABLE 85 110 30 5 0
PACKET WIDTH 85mm 85 110 50 8 2
FLOWER 100 125 10 2 0
HERB 100 125 50 10 0
PACKET WIDTH 100 125 60 12 0

Thanks for any extra input,

Rick