Mapping problem in nested view

1

I got an error (no error message) in a view that is based on a nested view but the data in the columns was in the wrong place. This problem suddenly occured in a system that has been running for several years.

I have not come across this error before and therefore I am a little lost.

I have a general fact view (view_base) containing common general fields och a view (view_special) that contains specialvalues for one type of data. These two views is then joined to a new view (view_all) containing all fields from both views.

the view was created according to below:

CREATE VIEW [view_all]
AS
SELECT f., r.
FROM view_special AS r
JOIN view_base AS f
ON r.s_id = f.id

The acute solution was to recreate the top view (view_all) and everything was fine again, but I need to reassure that this problem does not pop up again!

2

Are you sure this is exactly the view script? I'd expect SQL Server to throw an error when it encounters something like

SELECT f., r.

I don't know what caused the previous view to fail, but it should at least be created with this script:

CREATE VIEW [view_all]
AS
SELECT f.*
	,r.*
FROM view_special AS r
JOIN view_base AS f
	ON r.s_id = f.id

You should never use "tablename.*", but use the name of each and every column you want in the result, like tablename.first_name, tablename.last_name, ...

3

Thanks for the reply!
My query contains the asterisk just as you write, probably a copy/paste error when pasting to this thread.

Im into the same thoughts regarding r.* and f-* and to pinpoint the columnnames in the topview.
I cant see any other problem.

4

In DB2, every view became marked "inactive", as soon as the structure (columns) one of the tables or views it depended on was changed. After each ALTER TABLE (or VIEW) script we had to re-creatie all the views that directly or indirectly depended on that table or view. (That was 12 years ago. No idea if this is still the case.)

SQL Server doesn't do that. It will just happily execute the view definition, the moment it is used. You will only notice at execution time if the view is still executable or not.
Maybe the SELECT * has something to do with it. Did any of the used views (view_special or view_base) change? Like more columns or other data type?

5

No, they are built using a better syntax as below:
create view view_base
as
select
col1 as [Column 1]
,col2 as [Column 2]
.
from myTable

6

If you use * in a view - and one of the underlying tables/views changes the binding on the view will not change. For example, if someone inserted a new column in one of the underlying tables - the view will pull the data as if nothing changed.

So - all columns up to the newly available column will be correct, but then the new column would be returned for the next column and everything else will be shifted. This sounds like what you saw - and will be the result if you use * for the columns.