Subject | Re: [IBO] Params not updating in Select + Union |
---|---|
Author | Rick Roen |
Post date | 2005-10-09T14:02:12Z |
Thanks Helen for the detailed answer, please see my comments below.
I had thought about this, but it does perform as expected without
the INVOICENUM column, so I have omitted it.
are gone.
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?
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.
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
>masterlink. You
> 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
> would need to include INVOICENUM in both subsets and in the GROUPBY spec.
I had thought about this, but it does perform as expected without
the INVOICENUM column, so I have omitted it.
>perform
> 2. However, this doesn't totally solve the problem if you need to
> DML based on the values in the set, since you don't have anythingfrom
> which to make keylinks. For this query (though not genericallyfor union
> sets) the PK of the OrderItems table into the set would be neededin the
> spec and in the GROUP BY as well (for both subsets), in order toprovide 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 joincriterion. 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?
>(except
> 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
> 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.
>can never
> 5. Even without the complication of the union, a grouped query
> be an updatable detail set since, again, the set cannot provide adata
> access path to a single record on the server.must be
>
> 6. And note that, when creating the view, the ORDER BY clause
> omitted. It can, of course, be used in the SQL of the ib_query.you need
>
> However, I'd also want to review whether a union is really what
> for this set. Even with all the amendments suggest, the set wouldstill
> get you two records for each record in the underlying table. Ifthat's the
> purpose, OK, I guess.you
>
> If the actual intention is just to deliver the derived field, then
> 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.
>The union gives me a count of different groups of seed packets based
> Helen
>
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