#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    11
    Rep Power
    0

    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.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    250
    Rep Power
    15
    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
    Martijn Tonies
    Database Workbench: developer IDE for Firebird, MySQL, InterBase, MSSQL Server and Oracle
    Upscene Productions
    http://www.upscene.com
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    11
    Rep Power
    0
    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,
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    250
    Rep Power
    15
    >>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
    Martijn Tonies
    Database Workbench: developer IDE for Firebird, MySQL, InterBase, MSSQL Server and Oracle
    Upscene Productions
    http://www.upscene.com
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    11
    Rep Power
    0
    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.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    250
    Rep Power
    15
    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
    Martijn Tonies
    Database Workbench: developer IDE for Firebird, MySQL, InterBase, MSSQL Server and Oracle
    Upscene Productions
    http://www.upscene.com
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    11
    Rep Power
    0
    Thanks, Mr. Tonies,

    I try sending the data to Firebird Support.

    I appreciate your consistently quick advice.

    With regards,

IMN logo majestic logo threadwatch logo seochat tools logo