>Post an actual example here, maybe someone can provide an acceptable
>alternative. Dropping/recreating views doesn't sound like a good >solution.
>--
>Aage J.

It's not a good solution at all. Not only because I have to drop/recreate it each time (and sometimes an error is raised when trying to drop, saying the view is in use when it's not...but that's another issue), but specially because only one user can use the report on the same time as I cannot create the same view with two diferent date intervals. I know I could create another view, but droping/recrating one for each case of this is already enough for me :)

One example of this is a list sum of the sold quantity for each item between two dates. All the problem is if I don't use the view I cannot sum the quantity of one item sold on two or more different dates, it lists the same item more than once. So I use the view to make a pre-sum and then aggregate it on another query:

First I execute the drop:
DROP VIEW ITEMSVIEW;

Then I recreate it with the new date interval:
CREATE VIEW ITEMSVIEW (QTY, TOTAL, CODE, NAME, PRICE, BRAND) AS SELECT SUM(SI.TOTQTY), SUM(SI.TOTVL), I.CODE, I.DESCRIP, I.PRICE, B.BRANDNAME
FROM ((SALES S LEFT JOIN SALESITEMS SI ON S.CDSALES = SI.CDSALES) LEFT JOIN ITEMS I ON I.CODE = S.ITEMCODE) LEFT JOIN BRAND B ON B.CDBRAND = I.CDBRAND
GROUP BY I.CODE, I.DESCRIP, I.PRICE, B.BRANDNAME, S.SALEDATE HAVING S.SALEDATE BETWEEN '01/01/2004' AND '01/31/2004';

Then I make the grouping select:
SELECT SUM(QTY) AS TOTALQTY, SUM(TOTAL) AS TOTALVALUE, CODE, NAME, PRICE, BRAND FROM ITEMSVIEW GROUP BY CODE, NAME, PRICE, BRAND

Now each item has one overall sum of the quantity sold between the dates. Hope someone has a better solution...

>This feature is already in development (FB2.0 in sourceforge tree), thus
>you'll see it in the next firebird version, but don't expect it very soon.
>
>Arno Brinkman
>ABVisie

It's good news anyway! :)

Thanks for your help.

Luciano Enzweiler


[Non-text portions of this message have been removed]