October 20, 2005 at 4:40 pm
If Not Exists(Select * From Information_Schema.Views Where Table_Schema = 'Div2' And Table_Name = 'Categories')
Create View Div2.Categories As
Select
CT.Name Name
, IsNull(RCC.Parent, -1) Parent
, RCC.Description Description
, RCC.LevelNo LevelNo
, RCC.Fixed Fix
, RCD.Rank Rank
, RCD.Visible Visible
From
ConstType CT
Join CategoriesCommon RCC On RCC.ConstTypeId = CT.Id
Join CategoriesDivision RCD On RCD.CategoriesCommonId = RCC.Id
Where
(RCD.DivisionId = 2)
I'm too tired, maybe I'll be lucky and someone will see a syntax error.
Thanks for your help in advance,
Richard
October 20, 2005 at 4:59 pm
When I use Query Analyzer, it generates a BEGIN and END statement. Does this matter?
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [tbl] (
[field1] [decimal](11, 0) NULL ,
[field2] [varchar] (50) NULL
) ON [PRIMARY]
END
Michelle
October 20, 2005 at 5:22 pm
you forgot begin and end block
October 21, 2005 at 8:58 am
Hi,
I found out why. It turns out that "Create View" has to be the first statement of a batch. Its written up under "Create View" in Books Online. The following works:
If Exists(Select * From Information_Schema.Views Where Table_Schema = 'Div2' And Table_Name = 'Categories')
Drop View Div2.Categories
go
Create View Div2.Categories As
Select
CT.Name Name
, IsNull(RCC.Parent, -1) Parent
, RCC.Description Description
, RCC.LevelNo LevelNo
, RCC.Fixed Fix
, RCD.Rank Rank
, RCD.Visible Visible
From
ConstType CT
Join CategoriesCommon RCC On RCC.ConstTypeId = CT.Id
Join CategoriesDivision RCD On RCD.CategoriesCommonId = RCC.Id
Where
(RCD.DivisionId = 2)
Have a nice weekend,
Richard
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply