NEWBIE!!! Views and column names

Question

Post reply

NEWBIE!!! Views and column names

Pierrick Parigot

Grasshopper

Points: 12
More actions
January 28, 2009 at 10:27 am

#199476

Hi all,
I have inherited a MsSQL 2000 server with loads of views and stored procedures and so on.
I am working with a view (say view_example) and I am getting -what I would call- odd results. In EM, I get into the design mode for the view, and then I run the view from the design mode (! from the tool bar) and I get expected rows with the following columns:
salesnumber, itemnumber, qty_invoiced, qty_outstanding, salesprice, ATTENTION, lineval_net, lineval_gross, discpercent, vatpct
If I now run the same view_example in QA issueing the following:
SELECT * FROM database.dbo.view_example
I get the following columns:
salesnumber, itemnumber, qty_invoiced, qty_os, salesprice, weborder, lineval_net, lineval_gross, discpct, vatpct
Notice the different column names. Same data in the columns though.
Anything I am missing here?:crying:
Thanks
Kind regards
Pierrick
Kind regards
Pierrick

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

it sounds like the following occurred:

someone created the view, and when you select from the view, you get the results you saw.

Then, in Enterprise Manager, they edited the view, aliasing the columns with AS ATTENTION for example.

when you edit a view in EM, it does not run the ALTER VIEW AS statement...it just changes the code that is in syscomments.

if you run sp_refreshview YOURVIEWNAME, you'll get the new columns, as well as updated column descriptions.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Pierrick Parigot Grasshopper Points: 12 More actions · Answer 2

Lowell (1/28/2009)
it sounds like the following occurred:
someone created the view, and when you select from the view, you get the results you saw.
Then, in Enterprise Manager, they edited the view, aliasing the columns with AS ATTENTION for example.
when you edit a view in EM, it does not run the ALTER VIEW AS statement...it just changes the code that is in syscomments.
if you run sp_refreshview YOURVIEWNAME, you'll get the new columns, as well as updated column descriptions.

Hi Lowell,

Thanks for your reply. I ran the following statement:

exec sp_refreshview view_Example

in the appropriate database. Unfortunately it still came up with the same issue.

Guided by your suggestion I then looked at the code more closely and noticed a discrepancy between the SQL code in the design mode and one in the double-click dialog.

The design mode code starts like:

SELECT newid() as uid, salesnumber, slt.itemnumber, qty_invoiced, qty_outstanding, slt.salesprice,

ATTENTION, lineval_net, lineval_gross, slt.discpercent, vtc.vatpct

FROM.....

But the code in the dialog starts with:

CREATE VIEW view_OurExample

(uid, salesnumber, itemnumber, qty_invoiced, qty_os, salesprice, weborder, lineval_net, lineval_gross, discpct, vatpct)

AS

SELECT newid() as uid, salesnumber, slt.itemnumber, qty_invoiced, qty_outstanding, slt.salesprice,

ATTENTION, lineval_net, lineval_gross, slt.discpercent, vtc.vatpct

FROM.....

Spot the obvious...

Now I told you I'm a newbie!

Thanks heaps anyway.

Kind regards
Pierrick

twalston Mr or Mrs. 500 Points: 505 More actions · Answer 3

I do not have SQL Server 2000 to verify with...

In the create view statement the columns are being aliased.. It looks to me that the query designer is just showing you the results of the inner select statement.

You could modify the sql query to include the alias statements as part of the select to confirm.. i.e. select x as foo, y, z

Pierrick Parigot Grasshopper Points: 12 More actions · Answer 4

twalston (1/30/2009)
In the create view statement the columns are being aliased.. It looks to me that the query designer is just showing you the results of the inner select statement.
You could modify the sql query to include the alias statements as part of the select to confirm.. i.e. select x as foo, y, z

Hi twalston,

Thanks for your suggestion.

You are right of course. My previous post was simply to show where I had made the mistake: looking at the designer SQL code only and not realising the mismatch with the CREATE VIEW statement in the view properties dialog box.

I have now aliased the columns in the SELECT statement to match those in the CREATE VIEW statement. It's all consistent now.

Kind regards
Pierrick