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?

Thanks, UN

asked 12 Sep '15, 02:19

unle70's gravatar image

unle70
78114
accept rate: 100%

What is the numeric SQLCODE for the error?

Please show us a screen shot of the error message.

(12 Sep '15, 09:05) Breck Carter

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.

permanent link

answered 14 Sep '15, 05:28

unle70's gravatar image

unle70
78114
accept rate: 100%

Looks like a View is behaving slightly different than the underlying SELECT phrase when entered in Interactive SQL.

AFAIK, with the exception of different permissions, that should not be the case - the query optimizer will expand a reqular view by "replacing" it with it's definition, cf. the term "(non-materialized) view expansion" in that doc topic.

Or is there a different setting of option "divide_by_zero_error" for the interactive user and the view owner?

(14 Sep '15, 06:08) Volker Barth

Your original question did not provide enough information, you ignored the request for more information, and your explanation makes absolutely no sense whatsoever (Volker is being polite).

I'm sure you had the problem you describe, but your description is of absolutely no use... hence the down votes.

The purpose of this forum is two-fold: (1) to provide immediate assistance, and (2) to help other people in the future. This thread fails to meet either purpose.

(14 Sep '15, 08:06) Breck Carter
1

Well, both of you are very knowledgeable and polite, but the facts remain: 1. The original usage of the View failed without returning any error number, just a message (in Chinese) saying something about not being able to continue. 2. Running the underlying SQL phrase (of which the View is constructed) returned good results without getting stuck. 3. Luckily (for me), this View was also used in one stored procedure. When running this stored procedure, it returned a "-628" error (Division by zero), which was my hint for what's going on. 4. After guessing where in the View this division by zero might have happened and preventing it, both the View and procedure returned back to work as normal.

Indeed the problem was not related to the size of the tables (as I originally thought), but to some specific data values that never existed until now.

Sorry if this is not helping other people.

(14 Sep '15, 09:18) unle70

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.

permanent link

answered 14 Sep '15, 12:27

Siger%20Matt's gravatar image

Siger Matt
3.1k486493
accept rate: 13%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×124
×47
×25

question asked: 12 Sep '15, 02:19

question was seen: 1,392 times

last updated: 14 Sep '15, 12:27