view referencing another view

With firebird 1.5, views referencing another view raises
exceptions. Is there anybody experiencing this bug?
Actually, this is quite a fundamental deficiency as a relational
database and very disappointing. I sincerely hope this bug
will be crashed as soon as possible.

Hi,

This works fine here.

Can you give us a reproducable case?

Or at least an example of a failure?

--
With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL Server.
Upscene Productions
http://www.upscene.com

Thank you, Mr. Martijn Tonies,

I have a "sales" table with fields of "seller", "customer",
"item","sales","profit". The "sales" and "profit" field data type are double precision and other field data types are all varchar.

This table contains all sale activity record. And I first created the following view, using IBExpert personal.

1.
CREATE VIEW VI_TOTALPROFITSALES_BY_CUSTOMER(
CUSTOMER, PROFIT, SALES) AS
select customer, sum(profit), sum(sales) from sales
group by customer;

And then I create the following view.

2.
CREATE VIEW VI_PROFIT_RATE_BY_CUSTOMER(
CUSTOMER, PROFIT_RATE) AS
select customer, profit/sales*100 from vi_totalprofitsales_by_customer where sales <> 0;

And then, I created another view referencing the
above view as follows.

3.
CREATE VIEW VI_PROFITRATE_RANK_BY_CUSOTMER(
CUSTOMER,
PROFIT_RATE,
RANKING)
AS
select s.customer, s.profit_rate,
( select count(t.profit_rate) from vi_profit_rate_by_customer t
where s.profit_rate >= t.profit_rate) prate
from vi_profit_rate_by_customer s where s.profit_rate is not null;

And this last view raises the following exception:

"Arithmetic overflow or division by zero has occurred.
arithmetic exception, numeric overflow or string truncation."

Please let me know if you have a solution.

With regards,

>>Please let me know if you have a solution

Not with only this info.

I'm just saying, that in general, this should work fine. I suggest you post your question -- with the table metadata and some sample data -- to the Firebird support list at Yahoogroups.com

Also, you might want to try and see if this error is data related.

Or do you get this error while creating the 3rd view?


With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL Server.
Upscene Productions
http://www.upscene.com

Thanks, Mr. Tonies,

I did not know where to send the relevant meta data.
Can I send the data to you alo by e-mail? But the data
are a lot...

Incidentally, for additional information, when inserting all
the data of the 2nd view into a table, say, temptable,
the 3rd view gets desired results from the temptable properly. So, I suspect that this exception raising is irrelevant to the meta data.

Please let us know your idea.

Hi,

Post it to Firebird support - if this is a bug and reproducable with your metadata and sample data, it will be forwarded to the Firebird developers.

--
Martijn Tonies

Thanks, Mr. Tonies,

I try sending the data to Firebird Support.

I appreciate your consistently quick advice.

With regards,