SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Multiple statements
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

paintstripper
Starting Member

Australia
3 Posts

Posted - 10/22/2006 :  00:22:36  Show Profile  Reply with Quote
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)

Singapore
17638 Posts

Posted - 10/22/2006 :  01:59:18  Show Profile  Reply with Quote

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



KH

Go to Top of Page

paintstripper
Starting Member

Australia
3 Posts

Posted - 10/22/2006 :  22:16:32  Show Profile  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17638 Posts

Posted - 10/22/2006 :  22:23:22  Show Profile  Reply with Quote
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.




KH

Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 10/22/2006 :  23:01:38  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
>> 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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000