This "CREATE VIEW" doesn't work!!

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)

This "CREATE VIEW" doesn't work!!

Author

Topic

Chapelhillkid
Starting Member

3 Posts

Posted - 2005-03-08 : 04:25:11

Hello all,

I am trying to "create a view" that will get the total charge amount for a sale ths year, and the average total charge amount for each month this year, but my lack of skills is not enabling me to do this. I get a "'CREATE VIEW' must be the first statement in a query batch" message. I would be very appreciative of any help.

Create Table Members
(MemberNumber Char(4) Not Null,
MemJoinDate DateTime,
MemFirstName VarChar(20),
MemMiddleName VarChar(20),
MemLastName VarChar(20),
MemType Char(1),
MemAnnualIncome Money,
Primary Key (MemberNumber))

Create Table Charge
(ChargeNumber Integer Not Null,
ChargeDate DateTime,
ChargeDepartmentCode Char(3),
ChargeMemberNumber Char(4),
Primary Key (ChargeNumber))

Create Table Department
(DepartmentCode Char(3) Not Null,
DepartmentName VarChar(20),
Primary Key (DepartmentCode))

Create Table ChargeLineItem
(CLIChargeNumber Integer Not Null,
CLIStkNumber Char(4) Not Null,
CLILinePrice Money,
CLILineQty Integer,
Primary Key (CLIChargeNumber, CLIStkNumber))

Create Table Stock
(StkNumber Char(4) Not Null,
StkName VarChar(40),
StkPrice Money,
Primary Key (StkNumber))

Insert Into Members Values("1000","1/13/04","John","William","Smith","F",300000)
Insert Into Members Values("1001","8/12/02","Allison","Marie","Cooper","H",550000)
Insert Into Members Values("1002","1/19/04","John","Henry","Smith","F",750000)
Insert Into Members Values("1003","1/29/03","Kathryn","Mary","Schumann","S",120000)
Insert Into Members Values("1004","4/30/03","Ira","Len","Rhodes","F",300000)
Insert Into Members Values("1005","12/15/03","Michael","Linwood","Ballard","H",100000)
Insert Into Members Values("1006","10/25/03","Della","Marie","Harrell","S",70000)

Insert Into Department Values("100","DiningRoom")
Insert Into Department Values("200","Grill")
Insert Into Department Values("300","HealthClub")
Insert Into Department Values("400","ProShop")
Insert Into Department Values("500","GolfSnackShop")
Insert Into Department Values("600","PoolSnackBar")

Insert Into Stock Values("1000","GolfRound18",40.00)
Insert Into Stock Values("1001","GolfRound9",20.00)
Insert Into Stock Values("1002","TitalistGolfBalls",5.00)
Insert Into Stock Values("1003","CartRental",10.00)
Insert Into Stock Values("2000","SwimTowelRental",10.00)
Insert Into Stock Values("2001","SwimGoggles",3.00)
Insert Into Stock Values("2002","SwimFloatRental",3.00)
Insert Into Stock Values("3000","TennisRacketRental",5.00)
Insert Into Stock Values("3001","TennisBallRental",3.00)
Insert Into Stock Values("4000","RaquetballRacketRental",5.00)
Insert Into Stock Values("4001","RaquetballBallRental",3.00)
Insert Into Stock Values("5000","Fries",1.50)
Insert Into Stock Values("5100","CokeClassic",1.25)
Insert Into Stock Values("5101","DietCoke",1.25)
Insert Into Stock Values("5200","ChickenSandwich",4.00)
Insert Into Stock Values("5300","Hamburger",2.95)
Insert Into Stock Values("5330","Cheeseburger",3.10)
Insert Into Stock Values("5332","DoubleCheeseburger",3.50)

Insert Into Charge Values(100,"1/13/04","100","1001")
Insert Into Charge Values(101,"2/01/04","200","1004")
Insert Into Charge Values(102,"12/30/03","400","1002")
Insert Into Charge Values(103,"1/12/04","200","1003")
Insert Into Charge Values(104,"1/20/04","300","1005")
Insert Into Charge Values(105,"2/15/04","400","1006")
Insert Into Charge Values(106,"11/28/03","200","1002")
Insert Into Charge Values(107,"1/12/04","400","1003")
Insert Into Charge Values(108,"2/24/04","400","1002")
Insert Into Charge Values(109,"2/14/04","400","1001")

Insert Into ChargeLineItem Values(100,"5332",3.50,2)
Insert Into ChargeLineItem Values(100,"5101",1.25,2)
Insert Into ChargeLineItem Values(101,"5332",3.50,1)
Insert Into ChargeLineItem Values(102,"1000",40.00,2)
Insert Into ChargeLineItem Values(103,"5332",3.50,1)
Insert Into ChargeLineItem Values(103,"5000",1.50,2)
Insert Into ChargeLineItem Values(104,"5101",1.25,1)
Insert Into ChargeLineItem Values(105,"1001",20.00,3)
Insert Into ChargeLineItem Values(106,"5332",3.50,2)
Insert Into ChargeLineItem Values(107,"1000",40.00,1)
Insert Into ChargeLineItem Values(108,"1001",20.00,2)
Insert Into ChargeLineItem Values(109,"1001",20.00,1)

Create View TotalChargeAmountForEachSale As
Select ChargeNumber, SUM(CLILinePrice*CLILineQty) As "TotalCharge", ChargeDate
From Charge, ChargeLineItem
Where CLIChargeNumber = ChargeNumber
And Year(ChargeDate) = Year(GetDate())
Group By ChargeNumber, ChargeDate

Select AVG(TotalCharge) as AvgMonthlyCharge, Month(ChargeDate) As "Month Number",
DateName(Month, ChargeDate) As "Month Name"
From TotalChargeAmountForEachSale
Where Year(ChargeDate) = Year(GetDate())
Group By Month(ChargeDate), DateName(Month, ChargeDate)
Order By Month(ChargeDate)

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-03-08 : 04:29:28

Try separating each statement with a GO

EG:
Create View TotalChargeAmountForEachSale As
Select ChargeNumber, SUM(CLILinePrice*CLILineQty) As "TotalCharge", ChargeDate
From Charge, ChargeLineItem
Where CLIChargeNumber = ChargeNumber
And Year(ChargeDate) = Year(GetDate())
Group By ChargeNumber, ChargeDate
GO

Select AVG(TotalCharge) as AvgMonthlyCharge, Month(ChargeDate) As "Month Number",
DateName(Month, ChargeDate) As "Month Name"
From TotalChargeAmountForEachSale
Where Year(ChargeDate) = Year(GetDate())
Group By Month(ChargeDate), DateName(Month, ChargeDate)
Order By Month(ChargeDate)
GO

Andy

Beauty is in the eyes of the beerholder

Chapelhillkid
Starting Member

3 Posts

Posted - 2005-03-08 : 04:33:36

still no success...

quote:
Originally posted by AndyB13

Try separating each statement with a GO

EG:
Create View TotalChargeAmountForEachSale As
Select ChargeNumber, SUM(CLILinePrice*CLILineQty) As "TotalCharge", ChargeDate
From Charge, ChargeLineItem
Where CLIChargeNumber = ChargeNumber
And Year(ChargeDate) = Year(GetDate())
Group By ChargeNumber, ChargeDate
GO

Select AVG(TotalCharge) as AvgMonthlyCharge, Month(ChargeDate) As "Month Number",
DateName(Month, ChargeDate) As "Month Name"
From TotalChargeAmountForEachSale
Where Year(ChargeDate) = Year(GetDate())
Group By Month(ChargeDate), DateName(Month, ChargeDate)
Order By Month(ChargeDate)
GO

Andy

Beauty is in the eyes of the beerholder

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-03-08 : 04:35:32

place a go before the "create view..." statements

--------------------
keeping it simple...

Chapelhillkid
Starting Member

3 Posts

Posted - 2005-03-08 : 04:47:48

Thanx guys for putting up with my lack of brain cells!!! U r amazing....

quote:
Originally posted by jen

place a go before the "create view..." statements

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources