Hello, I have a database where I set a View mainly for "Select" activities. This View is very simple and only collects together around 20 fields from various tables, connected by basic links. It was working fine for long time, until suddenly it stopped working, returning an error (can't read the error because it's a Chinese version).
I did some analysis and found out that if I Select only some of the fields from the View (SELECT a,b from xxx WHERE ...) then it works fine. But if I select ALL the fields (SELECT * from xxx WHERE ...) then it fails.
My guess is that the raw information in the tables became too large and so the View cannot handle them anymore. The "Where" statement which I use should actually return only a few records (1-5), but the raw data has accumulated until it got to be too large for the query to handle.
I would like to add that I'm using ODBC to access the database (if it means anything).
Any ideas from anyone?
asked 12 Sep '15, 02:19
Sorry, I solved it by myself. Looks like a View is behaving slightly different than the underlying SELECT phrase when entered in Interactive SQL. Eventually it was a "Divide by ZERO" error which was not nicely reflected when running the View, and for some reason never happened when running the same SELECT phrase (without the "View") in Interactive SQL.
answered 14 Sep '15, 05:28
I've seen something like this before but it is not due to the volume of data. DBISQL is set by default to only return the first 500 rows, so there are plenty of times when I have a bad data row with a divide by zero or a text field that is incorrectly included in a calculation. If that error is not in the first 500 rows, the view and / or statement will not error out in DBISQL.
Depending on the where clause and the ordering (either default or specified) you can create multiple scenarios that either have the error or do not, and chase wild geese. The underlying issue is what you found however, a bad data row.
answered 14 Sep '15, 12:27