Please, how to create "create view ...as"??? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

Please, how to create "create view ...as"???

Author

Topic

whitepear
Starting Member

24 Posts

Posted - 2014-04-12 : 03:40:55

hello!
Have this code, but does nto work!!!
Output table has 2 columns. name and sum(sum_price).

I want just max(sum(sum_price) to be outputted , but everything i try fails...

BEGIN TRANSACTION;

DROP TABLE TOPIC;
DROP TABLE LECTURE;
DROP TABLE PRICE;
DROP TABLE COURSE;

CREATE TABLE TOPIC(
ID_TOPIC INTEGER PRIMARY KEY,
NAME TEXT,
TK_ID_COURSE
);

CREATE TABLE LECTURE(
ID_LECTURE INTEGER PRIMARY KEY,
TK_ID_TOPIC INTEGER,
TK_ID_PRICE INTEGER
);

CREATE TABLE PRICE(
ID_PRICE INTEGER primary key,
RANG TEXT TEXT,
SUM_PRICE INTEGER
);

CREATE TABLE COURSE(
ID_COURSE INTEGER PRIMARY KEY,
NAME_COURSE TEXT
);

INSERT INTO TOPIC VALUES(1,'PIANNO',1);
INSERT INTO TOPIC VALUES(2,'VIOLA',1);
INSERT INTO TOPIC VALUES(3,'BMW',2);
INSERT INTO TOPIC VALUES(4,'AUDI',2);
INSERT INTO TOPIC VALUES(5,'DOGS',3);
INSERT INTO TOPIC VALUES(6,'CATS',3);
COMMIT;

INSERT INTO LECTURE VALUES(1,1,1);
INSERT INTO LECTURE VALUES(2,2,2);
INSERT INTO LECTURE VALUES(3,3,3);
INSERT INTO LECTURE VALUES(4,4,4);
INSERT INTO LECTURE VALUES(5,5,1);
INSERT INTO LECTURE VALUES(6,6,2);
INSERT INTO LECTURE VALUES(7,1,3);
INSERT INTO LECTURE VALUES(8,2,4);
INSERT INTO LECTURE VALUES(9,3,1);
INSERT INTO LECTURE VALUES(10,4,2);
COMMIT;

INSERT INTO PRICE VALUES(1,'A',10);
INSERT INTO PRICE VALUES(2,'B',20);
INSERT INTO PRICE VALUES(3,'C',30);
INSERT INTO PRICE VALUES(4,'D',40);

COMMIT;

INSERT INTO COURSE VALUES(1,'MUSIC');
INSERT INTO COURSE VALUES(2,'CARS');
INSERT INTO COURSE VALUES(3,'ANIMALS');

CREATE VIEW TEMP1 AS
SELECT COURSE,SUM(SUM_PRICE) FROM TOPIC, LECTURE, PRICE, COURSE WHERE TOPIC.ID_TOPIC=LECTURE.TK_ID_TOPIC AND LECTURE.TK_ID_PRICE=PRICE.ID_PRICE AND COURSE.ID_COURSE=TOPIC.TK_ID_COURSE HAVING SUM(SUM_PRICE) > 30 ORDER BY NAME_COURSE DESC;

SELECT * FROM TEMP1;
-> and it just says no such a table: temp1???

MANY THANKS!!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-04-12 : 05:02:30

CREATE VIEW statement cannot be used withing a another batch.

GO
CREATE VIEW dbo.Temp1
AS

SELECT		c.Course,
		SUM(p.Sum_Price)
FROM		dbo.Topic AS t
INNER JOIN	dbo.Lecture AS l ON l.TK_ID_TOPIC = t.ID_TOPIC
INNER JOIN	dbo.Price AS p ON p.ID_PRICE = l.TK_ID_PRICE
INNER JOIN	dbo.Course AS c ON c.ID_COURSE = t.TK_ID_COURSE
HAVING		SUM(p.Sum_Price) > 30
ORDER BY	c.Course DESC;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources