At 09:18 PM 8/10/2005 +0000, you wrote:

>D7 + IBO 4.5B
>
>I have a master table for orders that several other queries get
>their INVOICENUM param from. The detail queries are both in the same
>data module and in others. Basically they all work as expected
>except one query that has a union. This particular IB_Query
>component does not update its params when the master table changes.
>I can update the :INVOICENUM param in code and everything is ok.
>
>Since the union is the only thing I can think of that is different,
>I am wondering if this could be a problem?
>
>Here is an outline of the SQL and the actual SQL is below in case I
>have missed something:
>
>pseudo SQL:
>
>Select Field1, Field2, ... from
> Table1 T1
> join Table2 on ...
> join Table3 on ...
>Where T1.INVOICENUM = :INVOICENUM
>Union
>Select Field1, Field2, ... from
> Table1 T1
> join Table2 on ...
> join Table3 on ...
>Where T1.INVOICENUM = :INVOICENUM
>Order by 2, 1
>
>I have the following settings for the TIB_QUERY:
>MasterSource: DMOrders.QOrders
>MasterLinks: ORDERITEMS.INVOICENUM=INVOICENUM
>AutoFetchAll: True
>RefreshOnParamChange: True
>KeyRelation: ORDERITEMS
>
>There are no Orderings set and no EditSQL, InsertSQL nor DeleteSQL.
>
>
>Any ideas why this particular param does not get updated?

As you supposed, it's the union that prevents this set from being a detail
set. You have tried to use the search parameter in each included set as
though it were a variable (which it isn't, of course) and then hoped that a
data access path would somehow be formed between the master set's primary
key and this "variable". But there is no single data access path to a
unioned set, even if all of the cursors in the set are over the same table
and have the same theoretical key (which, though not the case in the pseudo
SQL, is the case in the actual SQL).

If you want to implement a M/D for this set, you would need to make the
unioned set into a view. This will take care of the reading side of
things, since it ensures that the client has a single data access path
through the master link.

On the writing side of things, a unioned set is totally non-updatable, so
using KeyRelation achieves nothing. If you want to perform any DML on the
table beneath the unioned set, you will need CustomSQL for any operations
you want to do.

However, your "real" SQL has more problems, so simply making this into a
view won't work:

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.

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.

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).

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!!)

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