Multiple statements - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Multiple statements

Author

Topic

paintstripper
Starting Member

3 Posts

Posted - 2006-10-22 : 00:22:36

Is it possible to define a complex view in a query, then display the contents in the same query? I am having trouble making it work.

The view is as follows:

CREATE VIEW ownerprop (propertyno, ownerno, ownerfirstname, ownerlastname)
AS SELECT p.property_num, o.owner_num, o.first_name, o.last_name
From property_ p, owner o
where p.owner_num = o.owner_num
GROUP BY p.property_num, o.owner_num, o.first_name, o.last_name;

What would I put after the view to make it show?

Thanks,
Dan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-22 : 01:59:18

[code]
CREATE VIEW ownerprop ~~(propertyno, ownerno, ownerfirstname, ownerlastname)~~
AS
SELECT p.property_num as propertyno,
o.owner_num as ownerno,
o.first_name as ownerfirstname,
o.last_name as ownerlastname
From property_ p inner join owner o
~~where~~ On p.owner_num = o.owner_num
GROUP BY p.property_num, o.owner_num, o.first_name, o.last_name
[/code]

paintstripper
Starting Member

3 Posts

Posted - 2006-10-22 : 22:16:32

That code works great, but it still doesn't show the contents of the view after it has been created. How would I do this in the same query?
I can't get it to work at the moment, to look at the contents, I have to open a new query and type

Select *
From ownerprop;

Thanks,
Dan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-22 : 22:23:22

quote:
Originally posted by paintstripper

That code works great, but it still doesn't show the contents of the view after it has been created. How would I do this in the same query?
I can't get it to work at the moment, to look at the contents, I have to open a new query and type

Select *
From ownerprop;

Thanks,
Dan

CREATE VIEW will only create the view. It does not show the content.

Yes you have to do a select statement

select * from ownerprop

to see the records / content of the view.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-22 : 23:01:38

>> but it still doesn't show the contents of the view after it has been created.

If you want to get data, run as Tan suggested
If you want to know the content of the view, use

Sp_helptext 'ownerprop'

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources