Thread: Creation of VIEWS not working....
Dear List, This is my first post to the list, so forgive me if my etiquette is not correct, or this question has been answered before. I'm trying to convert an Oracle SQL script to postgresql. I have everything working in the script, except for the last part -- the creation of views. I get an error near the "CUSTNAME", and I don't know why. Is the syntax incorrect? I haven't altered this part yet. Thus it is the original code from the SQL*PLUS oracle database, which works. I have attached the file I'm trying to run. The Create View statement is at the bottom. If anyone can help, I'd appreciate it :-) (SEE ATTACHED: emp.sql) Many Thanks, -- Thomas Adam ===== Thomas Adam "The Linux Weekend Mechanic" -- www.linuxgazette.com __________________________________________________ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com-- set feedback off -- prompt Creating and populating tables and sequences. Please wait. CREATE TABLE DEPT ( DEPTNO INTEGER NOT NULL, DNAME VARCHAR(14), LOC VARCHAR(13), CONSTRAINT DEPT_PRIMARY_KEY PRIMARY KEY (DEPTNO)); INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK'); INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS'); INSERT INTO DEPT VALUES (30,'SALES','CHICAGO'); INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON'); CREATE TABLE EMP ( EMPNO INTEGER NOT NULL, ENAME VARCHAR(10), JOB VARCHAR(9), MGR INTEGER CONSTRAINT EMP_SELF_KEY REFERENCES EMP (EMPNO), HIREDATE VARCHAR(10), SAL DECIMAL(7,2), COMM DECIMAL(7,2), DEPTNO INTEGER NOT NULL, CONSTRAINT EMP_FOREIGN_KEY FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO), CONSTRAINT EMP_PRIMARY_KEY PRIMARY KEY (EMPNO)); INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'17-NOV-81',5000,NULL,10); INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'1-MAY-81',2850,NULL,30); INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'9-JUN-81',2450,NULL,10); INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'2-APR-81',2975,NULL,20); INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,'28-SEP-81',1250,1400,30); INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30); INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,'8-SEP-81',1500,0,30); INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,'3-DEC-81',950,NULL,30); INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'22-FEB-81',1250,500,30); INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'3-DEC-81',3000,NULL,20); INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20); INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'09-DEC-82',3000,NULL,20); INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'12-JAN-83',1100,NULL,20); INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'23-JAN-82',1300,NULL,10); CREATE TABLE BONUS ( ENAME VARCHAR(10), JOB CHAR(9), SAL INTEGER, COMM INTEGER); CREATE TABLE SALGRADE ( GRADE INTEGER, LOSAL INTEGER, HISAL INTEGER); INSERT INTO SALGRADE VALUES (1,700,1200); INSERT INTO SALGRADE VALUES (2,1201,1400); INSERT INTO SALGRADE VALUES (3,1401,2000); INSERT INTO SALGRADE VALUES (4,2001,3000); INSERT INTO SALGRADE VALUES (5,3001,9999); CREATE TABLE DUMMY ( DUMMY INTEGER ); INSERT INTO DUMMY VALUES (0); CREATE TABLE CUSTOMER ( CUSTID INTEGER NOT NULL, NAME VARCHAR(45), ADDRESS VARCHAR(40), CITY VARCHAR(30), STATE VARCHAR(2), ZIP VARCHAR(9), AREA INTEGER, PHONE VARCHAR(9), REPID INTEGER NOT NULL, CREDITLIMIT DECIMAL (9,2), COMMENTS TEXT, CONSTRAINT CUSTOMER_PRIMARY_KEY PRIMARY KEY (CUSTID), CONSTRAINT CUSTID_ZERO CHECK (CUSTID > 0)); CREATE TABLE ORD ( ORDID INTEGER NOT NULL, ORDERDATE DATE, COMMPLAN VARCHAR(1), CUSTID INTEGER NOT NULL, SHIPDATE DATE, TOTAL DECIMAL (8,2), CONSTRAINT TOTAL_ZERO CHECK (TOTAL >= 0), CONSTRAINT ORD_FOREIGN_KEY FOREIGN KEY (CUSTID) REFERENCES CUSTOMER (CUSTID), CONSTRAINT ORD_PRIMARY_KEY PRIMARY KEY (ORDID)); CREATE TABLE ITEM ( ORDID INTEGER NOT NULL, ITEMID INTEGER NOT NULL, PRODID INTEGER, ACTUALPRICE DECIMAL (8,2), QTY INTEGER, ITEMTOT DECIMAL (8,2), CONSTRAINT ITEM_FOREIGN_KEY FOREIGN KEY (ORDID) REFERENCES ORD (ORDID), CONSTRAINT ITEM_PRIMARY_KEY PRIMARY KEY (ORDID,ITEMID)); CREATE TABLE PRODUCT ( PRODID INTEGER CONSTRAINT PRODUCT_PRIMARY_KEY PRIMARY KEY, DESCRIP VARCHAR(30)); CREATE TABLE PRICE ( PRODID INTEGER NOT NULL, STDPRICE DECIMAL (8,2), MINPRICE DECIMAL (8,2), STARTDATE VARCHAR(10), -- formerly "DATE", ENDDATE VARCHAR(10)); -- formerly "DATE"); INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT, CITY, AREA, ADDRESS, COMMENTS) VALUES ('96711', 'CA', '7844', '598-6609', 'JOCKSPORTS', '100', '5000', 'BELMONT', '415', '345 VIEWRIDGE', 'Very friendly people to work with -- sales rep likes to be called Mike.'); INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT, CITY, AREA, ADDRESS, COMMENTS) VALUES ('94061', 'CA', '7521', '368-1223', 'TKB SPORT SHOP', '101', '10000', 'REDWOOD CITY', '415', '490 BOLI RD.', 'Rep called 5/8 about change in order - contact shipping.'); INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT, CITY, AREA, ADDRESS, COMMENTS) VALUES ('95133', 'CA', '7654', '644-3341', 'VOLLYRITE', '102', '7000', 'BURLINGAME', '415', '9722 HAMILTON', 'Company doing heavy promotion beginning 10/89. Prepare for large orders during winter.'); INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT, CITY, AREA, ADDRESS, COMMENTS) VALUES ('97544', 'CA', '7521', '677-9312', 'JUST TENNIS', '103', '3000', 'BURLINGAME', '415', 'HILLVIEW MALL', 'Contact rep about new line of tennis rackets.'); INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT, CITY, AREA, ADDRESS, COMMENTS) VALUES ('93301', 'CA', '7499', '996-2323', 'EVERY MOUNTAIN', '104', '10000', 'CUPERTINO', '408', '574 SURRY RD.', 'Customer with high market share (23%) due to aggressive advertising.'); INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT, CITY, AREA, ADDRESS, COMMENTS) VALUES ('91003', 'CA', '7844', '376-9966', 'K + T SPORTS', '105', '5000', 'SANTA CLARA', '408', '3476 EL PASEO', 'Tends to order large amounts of merchandise at once. Accounting is considering raising their credit limit. Usually pays on time.'); INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT, CITY, AREA, ADDRESS, COMMENTS) VALUES ('94301', 'CA', '7521', '364-9777', 'SHAPE UP', '106', '6000', 'PALO ALTO', '415', '908 SEQUOIA', 'Support intensive. Orders small amounts (< 800) of merchandise at a time.'); INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT, CITY, AREA, ADDRESS, COMMENTS) VALUES ('93301', 'CA', '7499', '967-4398', 'WOMENS SPORTS', '107', '10000', 'SUNNYVALE', '408', 'VALCO VILLAGE', 'First sporting goods store geared exclusively towards women. Unusual promotion al style and very willing to take chances towards new products!'); INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT, CITY, AREA, ADDRESS, COMMENTS) VALUES ('55649', 'MN', '7844', '566-9123', 'NORTH WOODS HEALTH AND FITNESS SUPPLY CENTER', '108', '8000', 'HIBBING', '612', '98 LONE PINE WAY', ''); INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) VALUES ('101.4', '08-JAN-87', '610', '07-JAN-87', '101', 'A'); INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) VALUES ('45', '11-JAN-87', '611', '11-JAN-87', '102', 'B'); INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) VALUES ('5860', '20-JAN-87', '612', '15-JAN-87', '104', 'C'); INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) VALUES ('2.4', '30-MAY-86', '601', '01-MAY-86', '106', 'A'); INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) VALUES ('56', '20-JUN-86', '602', '05-JUN-86', '102', 'B'); INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) VALUES ('698', '30-JUN-86', '604', '15-JUN-86', '106', 'A'); INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) VALUES ('8324', '30-JUL-86', '605', '14-JUL-86', '106', 'A'); INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) VALUES ('3.4', '30-JUL-86', '606', '14-JUL-86', '100', 'A'); INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) VALUES ('97.5', '15-AUG-86', '609', '01-AUG-86', '100', 'B'); INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) VALUES ('5.6', '18-JUL-86', '607', '18-JUL-86', '104', 'C'); INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) VALUES ('35.2', '25-JUL-86', '608', '25-JUL-86', '104', 'C'); INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) VALUES ('224', '05-JUN-86', '603', '05-JUN-86', '102', ''); INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) VALUES ('4450', '12-MAR-87', '620', '12-MAR-87', '100', ''); INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) VALUES ('6400', '01-FEB-87', '613', '01-FEB-87', '108', ''); INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) VALUES ('23940', '05-FEB-87', '614', '01-FEB-87', '102', ''); INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) VALUES ('764', '10-FEB-87', '616', '03-FEB-87', '103', ''); INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) VALUES ('1260', '04-FEB-87', '619', '22-FEB-87', '104', ''); INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) VALUES ('46370', '03-MAR-87', '617', '05-FEB-87', '105', ''); INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) VALUES ('710', '06-FEB-87', '615', '01-FEB-87', '107', ''); INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) VALUES ('3510.5', '06-MAR-87', '618', '15-FEB-87', '102', 'A'); INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) VALUES ('730', '01-JAN-87', '621', '15-MAR-87', '100', 'A'); INSERT INTO ITEM (QTY, PRODID, ORDID, ITEMTOT, ITEMID, ACTUALPRICE) VALUES ('1', '100890', '610', '58', '3', '58'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ( '1', '100861', '611', '45', '1', '45'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ( '100', '100860', '612', '3000', '1', '30'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ( '1', '200376', '601', '2.4', '1', '2.4'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ( '20', '100870', '602', '56', '1', '2.8'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ( '3', '100890', '604', '174', '1', '58'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ( '2', '100861', '604', '84', '2', '42'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ( '10', '100860', '604', '440', '3', '44'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ( '4', '100860', '603', '224', '2', '56'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ( '1', '100860', '610', '35', '1', '35'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ( '3', '100870', '610', '8.4', '2', '2.8'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ( '200', '200376', '613', '440', '4', '2.2'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ( '444', '100860', '614', '15540', '1', '35'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ( '1000', '100870', '614', '2800', '2', '2.8'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ( '20', '100861', '612', '810', '2', '40.5'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('150', '101863', '612', '1500', '3', '10'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('10', '100860', '620', '350', '1', '35'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('1000', '200376', '620', '2400', '2', '2.4'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('500', '102130', '620', '1700', '3', '3.4'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ( '100', '100871', '613', '560', '1', '5.6'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('200', '101860', '613', '4800', '2', '24'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('150', '200380', '613', '600', '3', '4'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('100', '102130', '619', '340', '3', '3.4'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('50', '100860', '617', '1750', '1', '35'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('100', '100861', '617', '4500', '2', '45'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('1000', '100871', '614', '5600', '3', '5.6'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('10', '100861', '616', '450', '1', '45'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('50', '100870', '616', '140', '2', '2.8'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('2', '100890', '616', '116', '3', '58'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('10', '102130', '616', '34', '4', '3.4'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('10', '200376' , '616', '24', '5', '2.4'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('100', '200380', '619', '400', '1', '4'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('100', '200376', '619', '240', '2', '2.4'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('4', '100861', '615', '180', '1', '45'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('1', '100871', '607', '5.6', '1', '5.6'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('100', '100870', '615', '280', '2', '2.8'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('500', '100870', '617', '1400', '3', '2.8'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('500', '100871', '617', '2800', '4', '5.6'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('500', '100890', '617', '29000', '5', '58'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('100', '101860', '617', '2400', '6', '24'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('200', '101863', '617', '2500', '7', '12.5'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('100', '102130', '617', '340', '8', '3.4'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('200', '200376', '617', '480', '9', '2.4'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('300', '200380', '617', '1200', '10', '4'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('5', '100870', '609', '12.5', '2', '2.5'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('1', '100890', '609', '50', '3', '50'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('23', '100860', '618', '805', '1', '35'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('50', '100861', '618', '2255.5', '2', '45.11'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('10', '100870', '618', '450', '3', '45'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('10', '100861', '621', '450', '1', '45'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('100', '100870', '621', '280', '2', '2.8'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('50', '100871', '615', '250', '3', '5'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('1', '101860', '608', '24', '1', '24'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('2', '100871', '608', '11.2', '2', '5.6'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('1', '100861', '609', '35', '1', '35'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('1', '102130', '606', '3.4', '1', '3.4'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('100', '100861', '605', '4500', '1', '45'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('500', '100870', '605', '1400', '2', '2.8'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('5', '100890', '605', '290', '3', '58'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('50', '101860', '605', '1200', '4', '24'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('100', '101863', '605', '900', '5', '9'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('10', '102130', '605', '34', '6', '3.4'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('100', '100871', '612', '550', '4', '5.5'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('50', '100871', '619', '280', '4', '5.6'); INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE) VALUES ('4.8', '01-JAN-85', '100871', '3.2', '01-DEC-85'); INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE) VALUES ('58', '01-JAN-85', '100890', '46.4', ''); INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE) VALUES ('54', '01-JUN-84', '100890', '40.5', '31-MAY-84'); INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE) VALUES ('35', '01-JUN-86', '100860', '28', ''); INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE) VALUES ('32', '01-JAN-86', '100860', '25.6', '31-MAY-86'); INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE) VALUES ('30', '01-JAN-85', '100860', '24', '31-DEC-85'); INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE) VALUES ('45', '01-JUN-86', '100861', '36', ''); INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE) VALUES ('42', '01-JAN-86', '100861', '33.6', '31-MAY-86'); INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE) VALUES ('39', '01-JAN-85', '100861', '31.2', '31-DEC-85'); INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE) VALUES ('2.8', '01-JAN-86', '100870', '2.4', ''); INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE) VALUES ('2.4', '01-JAN-85', '100870', '1.9', '01-DEC-85'); INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE) VALUES ('5.6', '01-JAN-86', '100871', '4.8', ''); INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE) VALUES ('24', '15-FEB-85', '101860', '18', ''); INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE) VALUES ('12.5', '15-FEB-85', '101863', '9.4', ''); INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE) VALUES ('3.4', '18-AUG-85', '102130', '2.8', ''); INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE) VALUES ('2.4', '15-NOV-86', '200376', '1.75', ''); INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE) VALUES ('4', '15-NOV-86', '200380', '3.2', ''); CREATE INDEX PRICE_INDEX ON PRICE(PRODID, STARTDATE); INSERT INTO PRODUCT (PRODID, DESCRIP) VALUES ('100860', 'ACE TENNIS RACKET I'); INSERT INTO PRODUCT (PRODID, DESCRIP) VALUES ('100861', 'ACE TENNIS RACKET II'); INSERT INTO PRODUCT (PRODID, DESCRIP) VALUES ('100870', 'ACE TENNIS BALLS-3 PACK'); INSERT INTO PRODUCT (PRODID, DESCRIP) VALUES ('100871', 'ACE TENNIS BALLS-6 PACK'); INSERT INTO PRODUCT (PRODID, DESCRIP) VALUES ('100890', 'ACE TENNIS NET'); INSERT INTO PRODUCT (PRODID, DESCRIP) VALUES ('101860', 'SP TENNIS RACKET'); INSERT INTO PRODUCT (PRODID, DESCRIP) VALUES ('101863', 'SP JUNIOR RACKET'); INSERT INTO PRODUCT (PRODID, DESCRIP) VALUES ('102130', 'RH: "GUIDE TO TENNIS"'); INSERT INTO PRODUCT (PRODID, DESCRIP) VALUES ('200376', 'SB ENERGY BAR-6 PACK'); INSERT INTO PRODUCT (PRODID, DESCRIP) VALUES ('200380', 'SB VITA SNACK-6 PACK'); CREATE SEQUENCE ORDID INCREMENT 1 START 622 CACHE 1; CREATE SEQUENCE PRODID INCREMENT 1 START 200381 CACHE 1; CREATE SEQUENCE CUSTID INCREMENT 1 START 109 CACHE 1; CREATE VIEW SALES AS SELECT REPID, ORD.CUSTID, CUSTOMER.NAME, PRODUCT.PRODID, CUSTNAME, PRODUCT.PRODID, DESCRIP PRODNAME, SUM(ITEMTOT) AMOUNT FROM ORD, ITEM, CUSTOMER, PRODUCT WHERE ORD.ORDID = ITEM.ORDID AND ORD.CUSTID = CUSTOMER.CUSTID AND ITEM.PRODID = PRODUCT.PRODID; GROUP BY REPID, ORD.CUSTID, NAME, PRODUCT.PRODID, DESCRIP; -- prompt Tables and sequences created and populated. -- set feedback on
Date sent: Mon, 3 Feb 2003 12:59:33 +0000 (GMT) From: Thomas Adam <thomas_adam16@yahoo.com> Subject: [NOVICE] Creation of VIEWS not working.... To: pgsql-general@postgresql.org Copies to: pgsql-novice@postgresql.org CREATE VIEW SALES AS SELECT REPID, ORD.CUSTID, CUSTOMER.NAME, PRODUCT.PRODID, CUSTNAME, PRODUCT.PRODID, DESCRIP PRODNAME, SUM(ITEMTOT) AMOUNT FROM ORD, ITEM, CUSTOMER, PRODUCT WHERE ORD.ORDID = ITEM.ORDID AND ORD.CUSTID = CUSTOMER.CUSTID AND ITEM.PRODID = PRODUCT.PRODID; <<- extra colon here I think GROUP BY REPID, ORD.CUSTID, NAME, PRODUCT.PRODID, DESCRIP; Dear List, This is my first post to the list, so forgive me if my etiquette is not correct, or this question has been answered before. I'm trying to convert an Oracle SQL script to postgresql. I have everything working in the script, except for the last part -- the creation of views. I get an error near the "CUSTNAME", and I don't know why. Is the syntax incorrect? I haven't altered this part yet. Thus it is the original code from the SQL*PLUS oracle database, which works. I have attached the file I'm trying to run. The Create View statement is at the bottom. If anyone can help, I'd appreciate it :-) (SEE ATTACHED: emp.sql) Many Thanks, -- Thomas Adam ===== Thomas Adam "The Linux Weekend Mechanic" -- www.linuxgazette.com __________________________________________________ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com
Date sent: Mon, 3 Feb 2003 12:59:33 +0000 (GMT) From: Thomas Adam <thomas_adam16@yahoo.com> Subject: [NOVICE] Creation of VIEWS not working.... To: pgsql-general@postgresql.org Copies to: pgsql-novice@postgresql.org This works theough whether it gives you what you want I've no idea, It looks to me that you have not placed 'AS' statements for aliases, put prodid in twice and the extra colon CREATE VIEW SALES AS SELECT REPID, ORD.CUSTID, CUSTOMER.NAME AS CUSTNAME, PRODUCT.PRODID, DESCRIP AS PRODNAME, SUM(ITEMTOT) AS AMOUNT FROM ORD, ITEM, CUSTOMER, PRODUCT WHERE ORD.ORDID = ITEM.ORDID AND ORD.CUSTID = CUSTOMER.CUSTID AND ITEM.PRODID = PRODUCT.PRODID GROUP BY REPID, ORD.CUSTID, NAME, PRODUCT.PRODID, DESCRIP; gives results: -[ RECORD 1 ]------------------------------------------ repid | 7499 custid | 104 custname | EVERY MOUNTAIN prodid | 100860 prodname | ACE TENNIS RACKET I amount | 3000.00 -[ RECORD 2 ]------------------------------------------ repid | 7499 custid | 104 custname | EVERY MOUNTAIN prodid | 100861 prodname | ACE TENNIS RACKET II amount | 810.00 -[ RECORD 3 ]------------------------------------------ repid | 7499 custid | 104 custname | EVERY MOUNTAIN prodid | 100871 prodname | ACE TENNIS BALLS-6 PACK amount | 846.80 Hope this helps Paul --0-579659294-1044277173=:58606 Content-Type: text/plain; charset=iso-8859-1 Content-Id: Content-Disposition: inline Content-Transfer-Encoding: quoted-printable Dear List, This is my first post to the list, so forgive me if my etiquette is not correct, or this question has been answered before. I'm trying to convert an Oracle SQL script to postgresql. I have everything working in the script, except for the last part -- the creation of views. I get an error near the "CUSTNAME", and I don't know why. Is the syntax incorrect? I haven't altered this part yet. Thus it is the original code from the SQL*PLUS oracle database, which works. I have attached the file I'm trying to run. The Create View statement is at the bottom. If anyone can help, I'd appreciate it :-) (SEE ATTACHED: emp.sql) Many Thanks, -- Thomas Adam =3D=3D=3D=3D=3D Thomas Adam "The Linux Weekend Mechanic" -- www.linuxgazette.com __________________________________________________ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com --0-579659294-1044277173=:58606 Content-Type: text/plain; name="emp.sql" Content-Description: emp.sql Content-Disposition: inline; filename="emp.sql" -- set feedback off -- prompt Creating and populating tables and sequences. Please wait. CREATE TABLE DEPT ( DEPTNO INTEGER NOT NULL, DNAME VARCHAR(14), LOC VARCHAR(13), CONSTRAINT DEPT_PRIMARY_KEY PRIMARY KEY (DEPTNO)); INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK'); INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS'); INSERT INTO DEPT VALUES (30,'SALES','CHICAGO'); INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON'); CREATE TABLE EMP ( EMPNO INTEGER NOT NULL, ENAME VARCHAR(10), JOB VARCHAR(9), MGR INTEGER CONSTRAINT EMP_SELF_KEY REFERENCES EMP (EMPNO), HIREDATE VARCHAR(10), SAL DECIMAL(7,2), COMM DECIMAL(7,2), DEPTNO INTEGER NOT NULL, CONSTRAINT EMP_FOREIGN_KEY FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO), CONSTRAINT EMP_PRIMARY_KEY PRIMARY KEY (EMPNO)); INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'17-NOV-81',5000,NULL,10); INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'1-MAY-81',2850,NULL,30); INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'9-JUN-81',2450,NULL,10); INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'2-APR-81',2975,NULL,20); INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,'28-SEP-81',1250,1400,30); INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30); INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,'8-SEP-81',1500,0,30); INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,'3-DEC-81',950,NULL,30); INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'22-FEB-81',1250,500,30); INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'3-DEC-81',3000,NULL,20); INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20); INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'09-DEC-82',3000,NULL,20); INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'12-JAN-83',1100,NULL,20); INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'23-JAN-82',1300,NULL,10); CREATE TABLE BONUS ( ENAME VARCHAR(10), JOB CHAR(9), SAL INTEGER, COMM INTEGER); CREATE TABLE SALGRADE ( GRADE INTEGER, LOSAL INTEGER, HISAL INTEGER); INSERT INTO SALGRADE VALUES (1,700,1200); INSERT INTO SALGRADE VALUES (2,1201,1400); INSERT INTO SALGRADE VALUES (3,1401,2000); INSERT INTO SALGRADE VALUES (4,2001,3000); INSERT INTO SALGRADE VALUES (5,3001,9999); CREATE TABLE DUMMY ( DUMMY INTEGER ); INSERT INTO DUMMY VALUES (0); CREATE TABLE CUSTOMER ( CUSTID INTEGER NOT NULL, NAME VARCHAR(45), ADDRESS VARCHAR(40), CITY VARCHAR(30), STATE VARCHAR(2), ZIP VARCHAR(9), AREA INTEGER, PHONE VARCHAR(9), REPID INTEGER NOT NULL, CREDITLIMIT DECIMAL (9,2), COMMENTS TEXT, CONSTRAINT CUSTOMER_PRIMARY_KEY PRIMARY KEY (CUSTID), CONSTRAINT CUSTID_ZERO CHECK (CUSTID > 0)); CREATE TABLE ORD ( ORDID INTEGER NOT NULL, ORDERDATE DATE, COMMPLAN VARCHAR(1), CUSTID INTEGER NOT NULL, SHIPDATE DATE, TOTAL DECIMAL (8,2), CONSTRAINT TOTAL_ZERO CHECK (TOTAL >= 0), CONSTRAINT ORD_FOREIGN_KEY FOREIGN KEY (CUSTID) REFERENCES CUSTOMER (CUSTID), CONSTRAINT ORD_PRIMARY_KEY PRIMARY KEY (ORDID)); CREATE TABLE ITEM ( ORDID INTEGER NOT NULL, ITEMID INTEGER NOT NULL, PRODID INTEGER, ACTUALPRICE DECIMAL (8,2), QTY INTEGER, ITEMTOT DECIMAL (8,2), CONSTRAINT ITEM_FOREIGN_KEY FOREIGN KEY (ORDID) REFERENCES ORD (ORDID), CONSTRAINT ITEM_PRIMARY_KEY PRIMARY KEY (ORDID,ITEMID)); CREATE TABLE PRODUCT ( PRODID INTEGER CONSTRAINT PRODUCT_PRIMARY_KEY PRIMARY KEY, DESCRIP VARCHAR(30)); CREATE TABLE PRICE ( PRODID INTEGER NOT NULL, STDPRICE DECIMAL (8,2), MINPRICE DECIMAL (8,2), STARTDATE VARCHAR(10), -- formerly "DATE", ENDDATE VARCHAR(10)); -- formerly "DATE"); INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT, CITY, AREA, ADDRESS, COMMENTS) VALUES ('96711', 'CA', '7844', '598-6609', 'JOCKSPORTS', '100', '5000', 'BELMONT', '415', '345 VIEWRIDGE', 'Very friendly people to work with -- sales rep likes to be called Mike.'); INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT, CITY, AREA, ADDRESS, COMMENTS) VALUES ('94061', 'CA', '7521', '368-1223', 'TKB SPORT SHOP', '101', '10000', 'REDWOOD CITY', '415', '490 BOLI RD.', 'Rep called 5/8 about change in order - contact shipping.'); INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT, CITY, AREA, ADDRESS, COMMENTS) VALUES ('95133', 'CA', '7654', '644-3341', 'VOLLYRITE', '102', '7000', 'BURLINGAME', '415', '9722 HAMILTON', 'Company doing heavy promotion beginning 10/89. Prepare for large orders during winter.'); INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT, CITY, AREA, ADDRESS, COMMENTS) VALUES ('97544', 'CA', '7521', '677-9312', 'JUST TENNIS', '103', '3000', 'BURLINGAME', '415', 'HILLVIEW MALL', 'Contact rep about new line of tennis rackets.'); INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT, CITY, AREA, ADDRESS, COMMENTS) VALUES ('93301', 'CA', '7499', '996-2323', 'EVERY MOUNTAIN', '104', '10000', 'CUPERTINO', '408', '574 SURRY RD.', 'Customer with high market share (23%) due to aggressive advertising.'); INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT, CITY, AREA, ADDRESS, COMMENTS) VALUES ('91003', 'CA', '7844', '376-9966', 'K + T SPORTS', '105', '5000', 'SANTA CLARA', '408', '3476 EL PASEO', 'Tends to order large amounts of merchandise at once. Accounting is considering raising their credit limit. Usually pays on time.'); INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT, CITY, AREA, ADDRESS, COMMENTS) VALUES ('94301', 'CA', '7521', '364-9777', 'SHAPE UP', '106', '6000', 'PALO ALTO', '415', '908 SEQUOIA', 'Support intensive. Orders small amounts (< 800) of merchandise at a time.'); INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT, CITY, AREA, ADDRESS, COMMENTS) VALUES ('93301', 'CA', '7499', '967-4398', 'WOMENS SPORTS', '107', '10000', 'SUNNYVALE', '408', 'VALCO VILLAGE', 'First sporting goods store geared exclusively towards women. Unusual promotion al style and very willing to take chances towards new products!'); INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT, CITY, AREA, ADDRESS, COMMENTS) VALUES ('55649', 'MN', '7844', '566-9123', 'NORTH WOODS HEALTH AND FITNESS SUPPLY CENTER', '108', '8000', 'HIBBING', '612', '98 LONE PINE WAY', ''); INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) VALUES ('101.4', '08-JAN-87', '610', '07-JAN-87', '101', 'A'); INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) VALUES ('45', '11-JAN-87', '611', '11-JAN-87', '102', 'B'); INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) VALUES ('5860', '20-JAN-87', '612', '15-JAN-87', '104', 'C'); INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) VALUES ('2.4', '30-MAY-86', '601', '01-MAY-86', '106', 'A'); INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) VALUES ('56', '20-JUN-86', '602', '05-JUN-86', '102', 'B'); INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) VALUES ('698', '30-JUN-86', '604', '15-JUN-86', '106', 'A'); INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) VALUES ('8324', '30-JUL-86', '605', '14-JUL-86', '106', 'A'); INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) VALUES ('3.4', '30-JUL-86', '606', '14-JUL-86', '100', 'A'); INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) VALUES ('97.5', '15-AUG-86', '609', '01-AUG-86', '100', 'B'); INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) VALUES ('5.6', '18-JUL-86', '607', '18-JUL-86', '104', 'C'); INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) VALUES ('35.2', '25-JUL-86', '608', '25-JUL-86', '104', 'C'); INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) VALUES ('224', '05-JUN-86', '603', '05-JUN-86', '102', ''); INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) VALUES ('4450', '12-MAR-87', '620', '12-MAR-87', '100', ''); INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) VALUES ('6400', '01-FEB-87', '613', '01-FEB-87', '108', ''); INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) VALUES ('23940', '05-FEB-87', '614', '01-FEB-87', '102', ''); INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) VALUES ('764', '10-FEB-87', '616', '03-FEB-87', '103', ''); INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) VALUES ('1260', '04-FEB-87', '619', '22-FEB-87', '104', ''); INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) VALUES ('46370', '03-MAR-87', '617', '05-FEB-87', '105', ''); INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) VALUES ('710', '06-FEB-87', '615', '01-FEB-87', '107', ''); INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) VALUES ('3510.5', '06-MAR-87', '618', '15-FEB-87', '102', 'A'); INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) VALUES ('730', '01-JAN-87', '621', '15-MAR-87', '100', 'A'); INSERT INTO ITEM (QTY, PRODID, ORDID, ITEMTOT, ITEMID, ACTUALPRICE) VALUES ('1', '100890', '610', '58', '3', '58'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ( '1', '100861', '611', '45', '1', '45'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ( '100', '100860', '612', '3000', '1', '30'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ( '1', '200376', '601', '2.4', '1', '2.4'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ( '20', '100870', '602', '56', '1', '2.8'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ( '3', '100890', '604', '174', '1', '58'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ( '2', '100861', '604', '84', '2', '42'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ( '10', '100860', '604', '440', '3', '44'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ( '4', '100860', '603', '224', '2', '56'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ( '1', '100860', '610', '35', '1', '35'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ( '3', '100870', '610', '8.4', '2', '2.8'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ( '200', '200376', '613', '440', '4', '2.2'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ( '444', '100860', '614', '15540', '1', '35'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ( '1000', '100870', '614', '2800', '2', '2.8'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ( '20', '100861', '612', '810', '2', '40.5'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('150', '101863', '612', '1500', '3', '10'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('10', '100860', '620', '350', '1', '35'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('1000', '200376', '620', '2400', '2', '2.4'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('500', '102130', '620', '1700', '3', '3.4'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ( '100', '100871', '613', '560', '1', '5.6'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('200', '101860', '613', '4800', '2', '24'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('150', '200380', '613', '600', '3', '4'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('100', '102130', '619', '340', '3', '3.4'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('50', '100860', '617', '1750', '1', '35'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('100', '100861', '617', '4500', '2', '45'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('1000', '100871', '614', '5600', '3', '5.6'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('10', '100861', '616', '450', '1', '45'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('50', '100870', '616', '140', '2', '2.8'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('2', '100890', '616', '116', '3', '58'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('10', '102130', '616', '34', '4', '3.4'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('10', '200376' , '616', '24', '5', '2.4'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('100', '200380', '619', '400', '1', '4'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('100', '200376', '619', '240', '2', '2.4'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('4', '100861', '615', '180', '1', '45'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('1', '100871', '607', '5.6', '1', '5.6'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('100', '100870', '615', '280', '2', '2.8'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('500', '100870', '617', '1400', '3', '2.8'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('500', '100871', '617', '2800', '4', '5.6'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('500', '100890', '617', '29000', '5', '58'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('100', '101860', '617', '2400', '6', '24'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('200', '101863', '617', '2500', '7', '12.5'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('100', '102130', '617', '340', '8', '3.4'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('200', '200376', '617', '480', '9', '2.4'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('300', '200380', '617', '1200', '10', '4'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('5', '100870', '609', '12.5', '2', '2.5'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('1', '100890', '609', '50', '3', '50'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('23', '100860', '618', '805', '1', '35'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('50', '100861', '618', '2255.5', '2', '45.11'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('10', '100870', '618', '450', '3', '45'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('10', '100861', '621', '450', '1', '45'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('100', '100870', '621', '280', '2', '2.8'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('50', '100871', '615', '250', '3', '5'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('1', '101860', '608', '24', '1', '24'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('2', '100871', '608', '11.2', '2', '5.6'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('1', '100861', '609', '35', '1', '35'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('1', '102130', '606', '3.4', '1', '3.4'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('100', '100861', '605', '4500', '1', '45'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('500', '100870', '605', '1400', '2', '2.8'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('5', '100890', '605', '290', '3', '58'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('50', '101860', '605', '1200', '4', '24'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('100', '101863', '605', '900', '5', '9'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('10', '102130', '605', '34', '6', '3.4'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('100', '100871', '612', '550', '4', '5.5'); INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) VALUES ('50', '100871', '619', '280', '4', '5.6'); INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE) VALUES ('4.8', '01-JAN-85', '100871', '3.2', '01-DEC-85'); INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE) VALUES ('58', '01-JAN-85', '100890', '46.4', ''); INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE) VALUES ('54', '01-JUN-84', '100890', '40.5', '31-MAY-84'); INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE) VALUES ('35', '01-JUN-86', '100860', '28', ''); INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE) VALUES ('32', '01-JAN-86', '100860', '25.6', '31-MAY-86'); INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE) VALUES ('30', '01-JAN-85', '100860', '24', '31-DEC-85'); INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE) VALUES ('45', '01-JUN-86', '100861', '36', ''); INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE) VALUES ('42', '01-JAN-86', '100861', '33.6', '31-MAY-86'); INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE) VALUES ('39', '01-JAN-85', '100861', '31.2', '31-DEC-85'); INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE) VALUES ('2.8', '01-JAN-86', '100870', '2.4', ''); INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE) VALUES ('2.4', '01-JAN-85', '100870', '1.9', '01-DEC-85'); INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE) VALUES ('5.6', '01-JAN-86', '100871', '4.8', ''); INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE) VALUES ('24', '15-FEB-85', '101860', '18', ''); INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE) VALUES ('12.5', '15-FEB-85', '101863', '9.4', ''); INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE) VALUES ('3.4', '18-AUG-85', '102130', '2.8', ''); INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE) VALUES ('2.4', '15-NOV-86', '200376', '1.75', ''); INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE) VALUES ('4', '15-NOV-86', '200380', '3.2', ''); CREATE INDEX PRICE_INDEX ON PRICE(PRODID, STARTDATE); INSERT INTO PRODUCT (PRODID, DESCRIP) VALUES ('100860', 'ACE TENNIS RACKET I'); INSERT INTO PRODUCT (PRODID, DESCRIP) VALUES ('100861', 'ACE TENNIS RACKET II'); INSERT INTO PRODUCT (PRODID, DESCRIP) VALUES ('100870', 'ACE TENNIS BALLS-3 PACK'); INSERT INTO PRODUCT (PRODID, DESCRIP) VALUES ('100871', 'ACE TENNIS BALLS-6 PACK'); INSERT INTO PRODUCT (PRODID, DESCRIP) VALUES ('100890', 'ACE TENNIS NET'); INSERT INTO PRODUCT (PRODID, DESCRIP) VALUES ('101860', 'SP TENNIS RACKET'); INSERT INTO PRODUCT (PRODID, DESCRIP) VALUES ('101863', 'SP JUNIOR RACKET'); INSERT INTO PRODUCT (PRODID, DESCRIP) VALUES ('102130', 'RH: "GUIDE TO TENNIS"'); INSERT INTO PRODUCT (PRODID, DESCRIP) VALUES ('200376', 'SB ENERGY BAR-6 PACK'); INSERT INTO PRODUCT (PRODID, DESCRIP) VALUES ('200380', 'SB VITA SNACK-6 PACK'); CREATE SEQUENCE ORDID INCREMENT 1 START 622 CACHE 1; CREATE SEQUENCE PRODID INCREMENT 1 START 200381 CACHE 1; CREATE SEQUENCE CUSTID INCREMENT 1 START 109 CACHE 1; CREATE VIEW SALES AS SELECT REPID, ORD.CUSTID, CUSTOMER.NAME, PRODUCT.PRODID, CUSTNAME, PRODUCT.PRODID, DESCRIP PRODNAME, SUM(ITEMTOT) AMOUNT FROM ORD, ITEM, CUSTOMER, PRODUCT WHERE ORD.ORDID = ITEM.ORDID AND ORD.CUSTID = CUSTOMER.CUSTID AND ITEM.PRODID = PRODUCT.PRODID; GROUP BY REPID, ORD.CUSTID, NAME, PRODUCT.PRODID, DESCRIP; -- prompt Tables and sequences created and populated. -- set feedback on --0-579659294-1044277173=:58606 Content-Type: text/plain Content-Disposition: inline Content-Transfer-Encoding: 8bit MIME-Version: 1.0 ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html --0-579659294-1044277173=:58606--
Paul, You are a genious :-) I'm at Southampton Uni, and unfortunately I cannot afford an Oracle database at home!! :-) With your efforts, I am now able to use postgresql on my Linux machine at home. THANKS. One other question, which is an aside from my initial query. In postgresql, when you run a script, I get "Notices" from the creation of foreign keys, etc. Can I set a feature in postgresql that runs a script in silent mode so that NOTHING is echoed to the screen unless I specifically embed a "\echo" string within my sql script? Thanks, -- Thomas Adam --- paul butler <paul@entropia.co.uk> wrote: > Date sent: Mon, 3 Feb 2003 12:59:33 +0000 > (GMT) > From: Thomas Adam > <thomas_adam16@yahoo.com> > Subject: [NOVICE] Creation of VIEWS not > working.... > To: pgsql-general@postgresql.org > Copies to: pgsql-novice@postgresql.org > > This works theough whether it gives you what you > want I've no idea, > It looks to me that you have not placed 'AS' > statements for aliases, > put prodid in twice and the extra colon > > CREATE VIEW SALES AS > SELECT REPID, ORD.CUSTID, CUSTOMER.NAME AS > CUSTNAME, PRODUCT.PRODID, > DESCRIP AS PRODNAME, SUM(ITEMTOT) AS AMOUNT > FROM ORD, ITEM, CUSTOMER, PRODUCT > WHERE ORD.ORDID = ITEM.ORDID > AND ORD.CUSTID = CUSTOMER.CUSTID > AND ITEM.PRODID = PRODUCT.PRODID > GROUP BY REPID, ORD.CUSTID, NAME, PRODUCT.PRODID, > DESCRIP; > > gives results: > > -[ RECORD 1 > ]------------------------------------------ > repid | 7499 > custid | 104 > custname | EVERY MOUNTAIN > prodid | 100860 > prodname | ACE TENNIS RACKET I > amount | 3000.00 > -[ RECORD 2 > ]------------------------------------------ > repid | 7499 > custid | 104 > custname | EVERY MOUNTAIN > prodid | 100861 > prodname | ACE TENNIS RACKET II > amount | 810.00 > -[ RECORD 3 > ]------------------------------------------ > repid | 7499 > custid | 104 > custname | EVERY MOUNTAIN > prodid | 100871 > prodname | ACE TENNIS BALLS-6 PACK > amount | 846.80 > > Hope this helps > > Paul > > > --0-579659294-1044277173=:58606 > Content-Type: text/plain; charset=iso-8859-1 > Content-Id: > Content-Disposition: inline > Content-Transfer-Encoding: quoted-printable > > Dear List, > > This is my first post to the list, so forgive me if > my > etiquette is not correct, or this question has been > answered before. > > I'm trying to convert an Oracle SQL script to > postgresql. I have everything working in the script, > except for the last part -- the creation of views. I > get an error near the "CUSTNAME", and I don't know > why. Is the syntax incorrect? I haven't altered this > part yet. Thus it is the original code from the > SQL*PLUS oracle database, which works. > > I have attached the file I'm trying to run. The > Create > View statement is at the bottom. If anyone can help, > I'd appreciate it :-) > > (SEE ATTACHED: emp.sql) > > Many Thanks, > > -- Thomas Adam > > =3D=3D=3D=3D=3D > Thomas Adam > > "The Linux Weekend Mechanic" -- www.linuxgazette.com > > __________________________________________________ > Do You Yahoo!? > Everything you'll ever need on one web page > from News and Sport to Email and Music Charts > http://uk.my.yahoo.com > --0-579659294-1044277173=:58606 > Content-Type: text/plain; name="emp.sql" > Content-Description: emp.sql > Content-Disposition: inline; filename="emp.sql" > > -- set feedback off > -- prompt Creating and populating tables and > sequences. Please wait. > > CREATE TABLE DEPT ( > DEPTNO INTEGER NOT NULL, > DNAME VARCHAR(14), > LOC VARCHAR(13), > CONSTRAINT DEPT_PRIMARY_KEY PRIMARY KEY (DEPTNO)); > > INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW > YORK'); > INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS'); > INSERT INTO DEPT VALUES (30,'SALES','CHICAGO'); > INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON'); > > CREATE TABLE EMP ( > EMPNO INTEGER NOT NULL, > ENAME VARCHAR(10), > JOB VARCHAR(9), > MGR INTEGER CONSTRAINT EMP_SELF_KEY > REFERENCES EMP (EMPNO), > HIREDATE VARCHAR(10), > SAL DECIMAL(7,2), > COMM DECIMAL(7,2), > DEPTNO INTEGER NOT NULL, > CONSTRAINT EMP_FOREIGN_KEY FOREIGN KEY (DEPTNO) > REFERENCES DEPT (DEPTNO), > CONSTRAINT EMP_PRIMARY_KEY PRIMARY KEY (EMPNO)); > > INSERT INTO EMP VALUES > (7839,'KING','PRESIDENT',NULL,'17-NOV-81',5000,NULL,10); > INSERT INTO EMP VALUES > (7698,'BLAKE','MANAGER',7839,'1-MAY-81',2850,NULL,30); > INSERT INTO EMP VALUES > (7782,'CLARK','MANAGER',7839,'9-JUN-81',2450,NULL,10); > INSERT INTO EMP VALUES > (7566,'JONES','MANAGER',7839,'2-APR-81',2975,NULL,20); > INSERT INTO EMP VALUES > (7654,'MARTIN','SALESMAN',7698,'28-SEP-81',1250,1400,30); > INSERT INTO EMP VALUES > (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30); > INSERT INTO EMP VALUES > (7844,'TURNER','SALESMAN',7698,'8-SEP-81',1500,0,30); > INSERT INTO EMP VALUES > (7900,'JAMES','CLERK',7698,'3-DEC-81',950,NULL,30); > INSERT INTO EMP VALUES > (7521,'WARD','SALESMAN',7698,'22-FEB-81',1250,500,30); > INSERT INTO EMP VALUES > (7902,'FORD','ANALYST',7566,'3-DEC-81',3000,NULL,20); > INSERT INTO EMP VALUES > (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20); > INSERT INTO EMP VALUES > (7788,'SCOTT','ANALYST',7566,'09-DEC-82',3000,NULL,20); > INSERT INTO EMP VALUES > (7876,'ADAMS','CLERK',7788,'12-JAN-83',1100,NULL,20); > INSERT INTO EMP VALUES > (7934,'MILLER','CLERK',7782,'23-JAN-82',1300,NULL,10); > > CREATE TABLE BONUS ( > ENAME VARCHAR(10), > JOB CHAR(9), > SAL INTEGER, > COMM INTEGER); > > CREATE TABLE SALGRADE ( > GRADE INTEGER, > LOSAL INTEGER, > HISAL INTEGER); > > INSERT INTO SALGRADE VALUES (1,700,1200); > INSERT INTO SALGRADE VALUES (2,1201,1400); > INSERT INTO SALGRADE VALUES (3,1401,2000); > INSERT INTO SALGRADE VALUES (4,2001,3000); > INSERT INTO SALGRADE VALUES (5,3001,9999); > > CREATE TABLE DUMMY ( > DUMMY INTEGER ); > > INSERT INTO DUMMY VALUES (0); > > CREATE TABLE CUSTOMER ( > CUSTID INTEGER NOT NULL, > NAME VARCHAR(45), > ADDRESS VARCHAR(40), > CITY VARCHAR(30), > STATE VARCHAR(2), > ZIP VARCHAR(9), > AREA INTEGER, > PHONE VARCHAR(9), > REPID INTEGER NOT NULL, > CREDITLIMIT DECIMAL (9,2), > COMMENTS TEXT, > CONSTRAINT CUSTOMER_PRIMARY_KEY PRIMARY KEY > (CUSTID), > CONSTRAINT CUSTID_ZERO CHECK (CUSTID > 0)); > === message truncated === ===== Thomas Adam "The Linux Weekend Mechanic" -- www.linuxgazette.com __________________________________________________ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com
Date sent: Mon, 3 Feb 2003 14:09:04 +0000 (GMT) From: Thomas Adam <thomas_adam16@yahoo.com> Subject: Re: [NOVICE] Creation of VIEWS not working.... THANKS :-) To: paul@entropia.co.uk Copies to: pgsql-novice@postgresql.org No Idea about suppressing notices, If you turn them off, you might miss them when you need them, if they're on, they don't do any real harm. Paul, You are a genious :-) I'm at Southampton Uni, and unfortunately I cannot afford an Oracle database at home!! :-) With your efforts, I am now able to use postgresql on my Linux machine at home. THANKS. One other question, which is an aside from my initial query. In postgresql, when you run a script, I get "Notices" from the creation of foreign keys, etc. Can I set a feature in postgresql that runs a script in silent mode so that NOTHING is echoed to the screen unless I specifically embed a "\echo" string within my sql script? Thanks, -- Thomas Adam --- paul butler <paul@entropia.co.uk> wrote: > Date sent: Mon, 3 Feb 2003 12:59:33 +0000 > (GMT) > From: Thomas Adam > <thomas_adam16@yahoo.com> > Subject: [NOVICE] Creation of VIEWS not > working.... > To: pgsql-general@postgresql.org > Copies to: pgsql-novice@postgresql.org > > This works theough whether it gives you what you > want I've no idea, > It looks to me that you have not placed 'AS' > statements for aliases, > put prodid in twice and the extra colon > > CREATE VIEW SALES AS > SELECT REPID, ORD.CUSTID, CUSTOMER.NAME AS > CUSTNAME, PRODUCT.PRODID, > DESCRIP AS PRODNAME, SUM(ITEMTOT) AS AMOUNT > FROM ORD, ITEM, CUSTOMER, PRODUCT > WHERE ORD.ORDID = ITEM.ORDID > AND ORD.CUSTID = CUSTOMER.CUSTID > AND ITEM.PRODID = PRODUCT.PRODID > GROUP BY REPID, ORD.CUSTID, NAME, PRODUCT.PRODID, > DESCRIP; > > gives results: > > -[ RECORD 1 > ]------------------------------------------ > repid | 7499 > custid | 104 > custname | EVERY MOUNTAIN > prodid | 100860 > prodname | ACE TENNIS RACKET I > amount | 3000.00 > -[ RECORD 2 > ]------------------------------------------ > repid | 7499 > custid | 104 > custname | EVERY MOUNTAIN > prodid | 100861 > prodname | ACE TENNIS RACKET II > amount | 810.00 > -[ RECORD 3 > ]------------------------------------------ > repid | 7499 > custid | 104 > custname | EVERY MOUNTAIN > prodid | 100871 > prodname | ACE TENNIS BALLS-6 PACK > amount | 846.80 > > Hope this helps > > Paul > > > --0-579659294-1044277173=:58606 > Content-Type: text/plain; charset=iso-8859-1 > Content-Id: > Content-Disposition: inline > Content-Transfer-Encoding: quoted-printable > > Dear List, > > This is my first post to the list, so forgive me if > my > etiquette is not correct, or this question has been > answered before. > > I'm trying to convert an Oracle SQL script to > postgresql. I have everything working in the script, > except for the last part -- the creation of views. I > get an error near the "CUSTNAME", and I don't know > why. Is the syntax incorrect? I haven't altered this > part yet. Thus it is the original code from the > SQL*PLUS oracle database, which works. > > I have attached the file I'm trying to run. The > Create > View statement is at the bottom. If anyone can help, > I'd appreciate it :-) > > (SEE ATTACHED: emp.sql) > > Many Thanks, > > -- Thomas Adam > > =3D=3D=3D=3D=3D > Thomas Adam > > "The Linux Weekend Mechanic" -- www.linuxgazette.com > > __________________________________________________ > Do You Yahoo!? > Everything you'll ever need on one web page > from News and Sport to Email and Music Charts > http://uk.my.yahoo.com > --0-579659294-1044277173=:58606 > Content-Type: text/plain; name="emp.sql" > Content-Description: emp.sql > Content-Disposition: inline; filename="emp.sql" > > -- set feedback off > -- prompt Creating and populating tables and > sequences. Please wait. > > CREATE TABLE DEPT ( > DEPTNO INTEGER NOT NULL, > DNAME VARCHAR(14), > LOC VARCHAR(13), > CONSTRAINT DEPT_PRIMARY_KEY PRIMARY KEY (DEPTNO)); > > INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW > YORK'); > INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS'); > INSERT INTO DEPT VALUES (30,'SALES','CHICAGO'); > INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON'); > > CREATE TABLE EMP ( > EMPNO INTEGER NOT NULL, > ENAME VARCHAR(10), > JOB VARCHAR(9), > MGR INTEGER CONSTRAINT EMP_SELF_KEY > REFERENCES EMP (EMPNO), > HIREDATE VARCHAR(10), > SAL DECIMAL(7,2), > COMM DECIMAL(7,2), > DEPTNO INTEGER NOT NULL, > CONSTRAINT EMP_FOREIGN_KEY FOREIGN KEY (DEPTNO) > REFERENCES DEPT (DEPTNO), > CONSTRAINT EMP_PRIMARY_KEY PRIMARY KEY (EMPNO)); > > INSERT INTO EMP VALUES > (7839,'KING','PRESIDENT',NULL,'17-NOV-81',5000,NULL,10); > INSERT INTO EMP VALUES > (7698,'BLAKE','MANAGER',7839,'1-MAY-81',2850,NULL,30); > INSERT INTO EMP VALUES > (7782,'CLARK','MANAGER',7839,'9-JUN-81',2450,NULL,10); > INSERT INTO EMP VALUES > (7566,'JONES','MANAGER',7839,'2-APR-81',2975,NULL,20); > INSERT INTO EMP VALUES > (7654,'MARTIN','SALESMAN',7698,'28-SEP-81',1250,1400,30); > INSERT INTO EMP VALUES > (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30); > INSERT INTO EMP VALUES > (7844,'TURNER','SALESMAN',7698,'8-SEP-81',1500,0,30); > INSERT INTO EMP VALUES > (7900,'JAMES','CLERK',7698,'3-DEC-81',950,NULL,30); > INSERT INTO EMP VALUES > (7521,'WARD','SALESMAN',7698,'22-FEB-81',1250,500,30); > INSERT INTO EMP VALUES > (7902,'FORD','ANALYST',7566,'3-DEC-81',3000,NULL,20); > INSERT INTO EMP VALUES > (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20); > INSERT INTO EMP VALUES > (7788,'SCOTT','ANALYST',7566,'09-DEC-82',3000,NULL,20); > INSERT INTO EMP VALUES > (7876,'ADAMS','CLERK',7788,'12-JAN-83',1100,NULL,20); > INSERT INTO EMP VALUES > (7934,'MILLER','CLERK',7782,'23-JAN-82',1300,NULL,10); > > CREATE TABLE BONUS ( > ENAME VARCHAR(10), > JOB CHAR(9), > SAL INTEGER, > COMM INTEGER); > > CREATE TABLE SALGRADE ( > GRADE INTEGER, > LOSAL INTEGER, > HISAL INTEGER); > > INSERT INTO SALGRADE VALUES (1,700,1200); > INSERT INTO SALGRADE VALUES (2,1201,1400); > INSERT INTO SALGRADE VALUES (3,1401,2000); > INSERT INTO SALGRADE VALUES (4,2001,3000); > INSERT INTO SALGRADE VALUES (5,3001,9999); > > CREATE TABLE DUMMY ( > DUMMY INTEGER ); > > INSERT INTO DUMMY VALUES (0); > > CREATE TABLE CUSTOMER ( > CUSTID INTEGER NOT NULL, > NAME VARCHAR(45), > ADDRESS VARCHAR(40), > CITY VARCHAR(30), > STATE VARCHAR(2), > ZIP VARCHAR(9), > AREA INTEGER, > PHONE VARCHAR(9), > REPID INTEGER NOT NULL, > CREDITLIMIT DECIMAL (9,2), > COMMENTS TEXT, > CONSTRAINT CUSTOMER_PRIMARY_KEY PRIMARY KEY > (CUSTID), > CONSTRAINT CUSTID_ZERO CHECK (CUSTID > 0)); > === message truncated === ===== Thomas Adam "The Linux Weekend Mechanic" -- www.linuxgazette.com __________________________________________________ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com
Just looks like typo (need to remove ;)and you need to use "as" and put CUSTNAME after CUSTOMER.NAME. Just a guest any way. NEW CREATE VIEW SALES AS SELECT REPID, ORD.CUSTID, CUSTOMER.NAME as CUSTNAME, PRODUCT.PRODID, PRODUCT.PRODID, DESCRIP as PRODNAME, SUM(ITEMTOT) as AMOUNT FROM ORD, ITEM, CUSTOMER, PRODUCT WHERE ORD.ORDID = ITEM.ORDID AND ORD.CUSTID = CUSTOMER.CUSTID AND ITEM.PRODID = PRODUCT.PRODID GROUP BY REPID, ORD.CUSTID, NAME, PRODUCT.PRODID, DESCRIP; OLD CREATE VIEW SALES AS SELECT REPID, ORD.CUSTID, CUSTOMER.NAME, PRODUCT.PRODID, CUSTNAME, PRODUCT.PRODID, DESCRIP PRODNAME, SUM(ITEMTOT) AMOUNT FROM ORD, ITEM, CUSTOMER, PRODUCT WHERE ORD.ORDID = ITEM.ORDID AND ORD.CUSTID = CUSTOMER.CUSTID AND ITEM.PRODID = PRODUCT.PRODID; GROUP BY REPID, ORD.CUSTID, NAME, PRODUCT.PRODID, DESCRIP; Regards, Simon Thomas Adam wrote: >Dear List, > >This is my first post to the list, so forgive me if my >etiquette is not correct, or this question has been >answered before. > >I'm trying to convert an Oracle SQL script to >postgresql. I have everything working in the script, >except for the last part -- the creation of views. I >get an error near the "CUSTNAME", and I don't know >why. Is the syntax incorrect? I haven't altered this >part yet. Thus it is the original code from the >SQL*PLUS oracle database, which works. > >I have attached the file I'm trying to run. The Create >View statement is at the bottom. If anyone can help, >I'd appreciate it :-) > >(SEE ATTACHED: emp.sql) > >Many Thanks, > >-- Thomas Adam > >===== >Thomas Adam > >"The Linux Weekend Mechanic" -- www.linuxgazette.com > >__________________________________________________ >Do You Yahoo!? >Everything you'll ever need on one web page >from News and Sport to Email and Music Charts >http://uk.my.yahoo.com > >------------------------------------------------------------------------ > >-- set feedback off >-- prompt Creating and populating tables and sequences. Please wait. > >CREATE TABLE DEPT ( > DEPTNO INTEGER NOT NULL, > DNAME VARCHAR(14), > LOC VARCHAR(13), > CONSTRAINT DEPT_PRIMARY_KEY PRIMARY KEY (DEPTNO)); > >INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK'); >INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS'); >INSERT INTO DEPT VALUES (30,'SALES','CHICAGO'); >INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON'); > >CREATE TABLE EMP ( > EMPNO INTEGER NOT NULL, > ENAME VARCHAR(10), > JOB VARCHAR(9), > MGR INTEGER CONSTRAINT EMP_SELF_KEY REFERENCES EMP (EMPNO), > HIREDATE VARCHAR(10), > SAL DECIMAL(7,2), > COMM DECIMAL(7,2), > DEPTNO INTEGER NOT NULL, > CONSTRAINT EMP_FOREIGN_KEY FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO), > CONSTRAINT EMP_PRIMARY_KEY PRIMARY KEY (EMPNO)); > >INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'17-NOV-81',5000,NULL,10); >INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'1-MAY-81',2850,NULL,30); >INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'9-JUN-81',2450,NULL,10); >INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'2-APR-81',2975,NULL,20); >INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,'28-SEP-81',1250,1400,30); >INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30); >INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,'8-SEP-81',1500,0,30); >INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,'3-DEC-81',950,NULL,30); >INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'22-FEB-81',1250,500,30); >INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'3-DEC-81',3000,NULL,20); >INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20); >INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'09-DEC-82',3000,NULL,20); >INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'12-JAN-83',1100,NULL,20); >INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'23-JAN-82',1300,NULL,10); > >CREATE TABLE BONUS ( > ENAME VARCHAR(10), > JOB CHAR(9), > SAL INTEGER, > COMM INTEGER); > >CREATE TABLE SALGRADE ( > GRADE INTEGER, > LOSAL INTEGER, > HISAL INTEGER); > >INSERT INTO SALGRADE VALUES (1,700,1200); >INSERT INTO SALGRADE VALUES (2,1201,1400); >INSERT INTO SALGRADE VALUES (3,1401,2000); >INSERT INTO SALGRADE VALUES (4,2001,3000); >INSERT INTO SALGRADE VALUES (5,3001,9999); > >CREATE TABLE DUMMY ( > DUMMY INTEGER ); > >INSERT INTO DUMMY VALUES (0); > >CREATE TABLE CUSTOMER ( > CUSTID INTEGER NOT NULL, > NAME VARCHAR(45), > ADDRESS VARCHAR(40), > CITY VARCHAR(30), > STATE VARCHAR(2), > ZIP VARCHAR(9), > AREA INTEGER, > PHONE VARCHAR(9), > REPID INTEGER NOT NULL, > CREDITLIMIT DECIMAL (9,2), > COMMENTS TEXT, > CONSTRAINT CUSTOMER_PRIMARY_KEY PRIMARY KEY (CUSTID), > CONSTRAINT CUSTID_ZERO CHECK (CUSTID > 0)); > >CREATE TABLE ORD ( > ORDID INTEGER NOT NULL, > ORDERDATE DATE, > COMMPLAN VARCHAR(1), > CUSTID INTEGER NOT NULL, > SHIPDATE DATE, > TOTAL DECIMAL (8,2), > > CONSTRAINT TOTAL_ZERO CHECK (TOTAL >= 0), > CONSTRAINT ORD_FOREIGN_KEY FOREIGN KEY (CUSTID) REFERENCES CUSTOMER (CUSTID), > CONSTRAINT ORD_PRIMARY_KEY PRIMARY KEY (ORDID)); > >CREATE TABLE ITEM ( > ORDID INTEGER NOT NULL, > ITEMID INTEGER NOT NULL, > PRODID INTEGER, > ACTUALPRICE DECIMAL (8,2), > QTY INTEGER, > ITEMTOT DECIMAL (8,2), > CONSTRAINT ITEM_FOREIGN_KEY FOREIGN KEY (ORDID) REFERENCES ORD (ORDID), > CONSTRAINT ITEM_PRIMARY_KEY PRIMARY KEY (ORDID,ITEMID)); > >CREATE TABLE PRODUCT ( > PRODID INTEGER CONSTRAINT PRODUCT_PRIMARY_KEY PRIMARY KEY, > DESCRIP VARCHAR(30)); > >CREATE TABLE PRICE ( > PRODID INTEGER NOT NULL, > STDPRICE DECIMAL (8,2), > MINPRICE DECIMAL (8,2), > STARTDATE VARCHAR(10), -- formerly "DATE", > ENDDATE VARCHAR(10)); -- formerly "DATE"); > >INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT, > CITY, AREA, ADDRESS, COMMENTS) >VALUES ('96711', 'CA', '7844', '598-6609', > 'JOCKSPORTS', > '100', '5000', 'BELMONT', '415', '345 VIEWRIDGE', > 'Very friendly people to work with -- sales rep likes to be called Mike.'); > >INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT, > CITY, AREA, ADDRESS, COMMENTS) >VALUES ('94061', 'CA', '7521', '368-1223', > 'TKB SPORT SHOP', > '101', '10000', 'REDWOOD CITY', '415', '490 BOLI RD.', > 'Rep called 5/8 about change in order - contact shipping.'); > >INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT, > CITY, AREA, ADDRESS, COMMENTS) >VALUES ('95133', 'CA', '7654', '644-3341', > 'VOLLYRITE', > '102', '7000', 'BURLINGAME', '415', '9722 HAMILTON', > 'Company doing heavy promotion beginning 10/89. Prepare for large orders during > winter.'); > >INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT, > CITY, AREA, ADDRESS, COMMENTS) >VALUES ('97544', 'CA', '7521', '677-9312', > 'JUST TENNIS', > '103', '3000', 'BURLINGAME', '415', 'HILLVIEW MALL', > 'Contact rep about new line of tennis rackets.'); > >INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT, > CITY, AREA, ADDRESS, COMMENTS) >VALUES ('93301', 'CA', '7499', '996-2323', > 'EVERY MOUNTAIN', > '104', '10000', 'CUPERTINO', '408', '574 SURRY RD.', > 'Customer with high market share (23%) due to aggressive advertising.'); > >INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT, > CITY, AREA, ADDRESS, COMMENTS) >VALUES ('91003', 'CA', '7844', '376-9966', > 'K + T SPORTS', > '105', '5000', 'SANTA CLARA', '408', '3476 EL PASEO', > 'Tends to order large amounts of merchandise at once. Accounting is considering > raising their credit limit. Usually pays on time.'); > >INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT, > CITY, AREA, ADDRESS, COMMENTS) >VALUES ('94301', 'CA', '7521', '364-9777', > 'SHAPE UP', > '106', '6000', 'PALO ALTO', '415', '908 SEQUOIA', > 'Support intensive. Orders small amounts (< 800) of merchandise at a time.'); > >INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT, > CITY, AREA, ADDRESS, COMMENTS) >VALUES ('93301', 'CA', '7499', '967-4398', > 'WOMENS SPORTS', > '107', '10000', 'SUNNYVALE', '408', 'VALCO VILLAGE', > 'First sporting goods store geared exclusively towards women. Unusual promotion >al style and very willing to take chances towards new products!'); > >INSERT INTO CUSTOMER (ZIP, STATE, REPID, PHONE, NAME, CUSTID, CREDITLIMIT, > CITY, AREA, ADDRESS, COMMENTS) >VALUES ('55649', 'MN', '7844', '566-9123', > 'NORTH WOODS HEALTH AND FITNESS SUPPLY CENTER', > '108', '8000', 'HIBBING', '612', '98 LONE PINE WAY', ''); > >INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) > VALUES ('101.4', '08-JAN-87', '610', '07-JAN-87', '101', 'A'); > >INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) > VALUES ('45', '11-JAN-87', '611', '11-JAN-87', '102', 'B'); > >INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) > VALUES ('5860', '20-JAN-87', '612', '15-JAN-87', '104', 'C'); > >INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) > VALUES ('2.4', '30-MAY-86', '601', '01-MAY-86', '106', 'A'); > >INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) > VALUES ('56', '20-JUN-86', '602', '05-JUN-86', '102', 'B'); > >INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) > VALUES ('698', '30-JUN-86', '604', '15-JUN-86', '106', 'A'); > >INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) > VALUES ('8324', '30-JUL-86', '605', '14-JUL-86', '106', 'A'); > >INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) > VALUES ('3.4', '30-JUL-86', '606', '14-JUL-86', '100', 'A'); > >INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) > VALUES ('97.5', '15-AUG-86', '609', '01-AUG-86', '100', 'B'); > >INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) > VALUES ('5.6', '18-JUL-86', '607', '18-JUL-86', '104', 'C'); > >INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) > VALUES ('35.2', '25-JUL-86', '608', '25-JUL-86', '104', 'C'); > >INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) > VALUES ('224', '05-JUN-86', '603', '05-JUN-86', '102', ''); > >INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) > VALUES ('4450', '12-MAR-87', '620', '12-MAR-87', '100', ''); > >INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) > VALUES ('6400', '01-FEB-87', '613', '01-FEB-87', '108', ''); > >INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) > VALUES ('23940', '05-FEB-87', '614', '01-FEB-87', '102', ''); > >INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) > VALUES ('764', '10-FEB-87', '616', '03-FEB-87', '103', ''); > >INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) > VALUES ('1260', '04-FEB-87', '619', '22-FEB-87', '104', ''); > >INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) > VALUES ('46370', '03-MAR-87', '617', '05-FEB-87', '105', ''); > >INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) > VALUES ('710', '06-FEB-87', '615', '01-FEB-87', '107', ''); > >INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) > VALUES ('3510.5', '06-MAR-87', '618', '15-FEB-87', '102', 'A'); > >INSERT INTO ORD (TOTAL, SHIPDATE, ORDID, ORDERDATE, CUSTID, COMMPLAN) > VALUES ('730', '01-JAN-87', '621', '15-MAR-87', '100', 'A'); > > >INSERT INTO ITEM (QTY, PRODID, ORDID, ITEMTOT, ITEMID, ACTUALPRICE) > VALUES ('1', '100890', '610', '58', '3', '58'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ( '1', '100861', '611', '45', '1', '45'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ( '100', '100860', '612', '3000', '1', '30'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ( '1', '200376', '601', '2.4', '1', '2.4'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ( '20', '100870', '602', '56', '1', '2.8'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ( '3', '100890', '604', '174', '1', '58'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ( '2', '100861', '604', '84', '2', '42'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ( '10', '100860', '604', '440', '3', '44'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ( '4', '100860', '603', '224', '2', '56'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ( '1', '100860', '610', '35', '1', '35'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ( '3', '100870', '610', '8.4', '2', '2.8'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ( '200', '200376', '613', '440', '4', '2.2'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ( '444', '100860', '614', '15540', '1', '35'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ( '1000', '100870', '614', '2800', '2', '2.8'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ( '20', '100861', '612', '810', '2', '40.5'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ('150', '101863', '612', '1500', '3', '10'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ('10', '100860', '620', '350', '1', '35'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ('1000', '200376', '620', '2400', '2', '2.4'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ('500', '102130', '620', '1700', '3', '3.4'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ( '100', '100871', '613', '560', '1', '5.6'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ('200', '101860', '613', '4800', '2', '24'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ('150', '200380', '613', '600', '3', '4'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ('100', '102130', '619', '340', '3', '3.4'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ('50', '100860', '617', '1750', '1', '35'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ('100', '100861', '617', '4500', '2', '45'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ('1000', '100871', '614', '5600', '3', '5.6'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ('10', '100861', '616', '450', '1', '45'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ('50', '100870', '616', '140', '2', '2.8'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ('2', '100890', '616', '116', '3', '58'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ('10', '102130', '616', '34', '4', '3.4'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ('10', '200376' , '616', '24', '5', '2.4'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ('100', '200380', '619', '400', '1', '4'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ('100', '200376', '619', '240', '2', '2.4'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ('4', '100861', '615', '180', '1', '45'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ('1', '100871', '607', '5.6', '1', '5.6'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ('100', '100870', '615', '280', '2', '2.8'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ('500', '100870', '617', '1400', '3', '2.8'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ('500', '100871', '617', '2800', '4', '5.6'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ('500', '100890', '617', '29000', '5', '58'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ('100', '101860', '617', '2400', '6', '24'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ('200', '101863', '617', '2500', '7', '12.5'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ('100', '102130', '617', '340', '8', '3.4'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ('200', '200376', '617', '480', '9', '2.4'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ('300', '200380', '617', '1200', '10', '4'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ('5', '100870', '609', '12.5', '2', '2.5'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ('1', '100890', '609', '50', '3', '50'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ('23', '100860', '618', '805', '1', '35'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ('50', '100861', '618', '2255.5', '2', '45.11'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ('10', '100870', '618', '450', '3', '45'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ('10', '100861', '621', '450', '1', '45'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ('100', '100870', '621', '280', '2', '2.8'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ('50', '100871', '615', '250', '3', '5'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ('1', '101860', '608', '24', '1', '24'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ('2', '100871', '608', '11.2', '2', '5.6'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ('1', '100861', '609', '35', '1', '35'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ('1', '102130', '606', '3.4', '1', '3.4'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ('100', '100861', '605', '4500', '1', '45'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ('500', '100870', '605', '1400', '2', '2.8'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ('5', '100890', '605', '290', '3', '58'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ('50', '101860', '605', '1200', '4', '24'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ('100', '101863', '605', '900', '5', '9'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ('10', '102130', '605', '34', '6', '3.4'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ('100', '100871', '612', '550', '4', '5.5'); > >INSERT INTO ITEM ( QTY , PRODID , ORDID , ITEMTOT , ITEMID , ACTUALPRICE) > VALUES ('50', '100871', '619', '280', '4', '5.6'); > > >INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE) > VALUES ('4.8', '01-JAN-85', '100871', '3.2', '01-DEC-85'); > >INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE) > VALUES ('58', '01-JAN-85', '100890', '46.4', ''); > >INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE) > VALUES ('54', '01-JUN-84', '100890', '40.5', '31-MAY-84'); > >INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE) > VALUES ('35', '01-JUN-86', '100860', '28', ''); > >INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE) > VALUES ('32', '01-JAN-86', '100860', '25.6', '31-MAY-86'); > >INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE) > VALUES ('30', '01-JAN-85', '100860', '24', '31-DEC-85'); > >INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE) > VALUES ('45', '01-JUN-86', '100861', '36', ''); > >INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE) > VALUES ('42', '01-JAN-86', '100861', '33.6', '31-MAY-86'); > >INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE) > VALUES ('39', '01-JAN-85', '100861', '31.2', '31-DEC-85'); > >INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE) > VALUES ('2.8', '01-JAN-86', '100870', '2.4', ''); > >INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE) > VALUES ('2.4', '01-JAN-85', '100870', '1.9', '01-DEC-85'); > >INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE) > VALUES ('5.6', '01-JAN-86', '100871', '4.8', ''); > >INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE) > VALUES ('24', '15-FEB-85', '101860', '18', ''); > >INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE) > VALUES ('12.5', '15-FEB-85', '101863', '9.4', ''); > >INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE) > VALUES ('3.4', '18-AUG-85', '102130', '2.8', ''); > >INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE) > VALUES ('2.4', '15-NOV-86', '200376', '1.75', ''); > >INSERT INTO PRICE (STDPRICE, STARTDATE, PRODID, MINPRICE, ENDDATE) > VALUES ('4', '15-NOV-86', '200380', '3.2', ''); > >CREATE INDEX PRICE_INDEX ON PRICE(PRODID, STARTDATE); > >INSERT INTO PRODUCT (PRODID, DESCRIP) > VALUES ('100860', 'ACE TENNIS RACKET I'); > >INSERT INTO PRODUCT (PRODID, DESCRIP) > VALUES ('100861', 'ACE TENNIS RACKET II'); > >INSERT INTO PRODUCT (PRODID, DESCRIP) > VALUES ('100870', 'ACE TENNIS BALLS-3 PACK'); > >INSERT INTO PRODUCT (PRODID, DESCRIP) > VALUES ('100871', 'ACE TENNIS BALLS-6 PACK'); > >INSERT INTO PRODUCT (PRODID, DESCRIP) > VALUES ('100890', 'ACE TENNIS NET'); > >INSERT INTO PRODUCT (PRODID, DESCRIP) > VALUES ('101860', 'SP TENNIS RACKET'); > >INSERT INTO PRODUCT (PRODID, DESCRIP) > VALUES ('101863', 'SP JUNIOR RACKET'); > >INSERT INTO PRODUCT (PRODID, DESCRIP) > VALUES ('102130', 'RH: "GUIDE TO TENNIS"'); > >INSERT INTO PRODUCT (PRODID, DESCRIP) > VALUES ('200376', 'SB ENERGY BAR-6 PACK'); > >INSERT INTO PRODUCT (PRODID, DESCRIP) > VALUES ('200380', 'SB VITA SNACK-6 PACK'); > > CREATE SEQUENCE ORDID > INCREMENT 1 > START 622 > CACHE 1; > > CREATE SEQUENCE PRODID > INCREMENT 1 > START 200381 > CACHE 1; > > CREATE SEQUENCE CUSTID > INCREMENT 1 > START 109 > CACHE 1; > > CREATE VIEW SALES AS > SELECT REPID, ORD.CUSTID, CUSTOMER.NAME, PRODUCT.PRODID, CUSTNAME, PRODUCT.PRODID, > DESCRIP PRODNAME, SUM(ITEMTOT) AMOUNT > FROM ORD, ITEM, CUSTOMER, PRODUCT > WHERE ORD.ORDID = ITEM.ORDID > AND ORD.CUSTID = CUSTOMER.CUSTID > AND ITEM.PRODID = PRODUCT.PRODID; > GROUP BY REPID, ORD.CUSTID, NAME, PRODUCT.PRODID, DESCRIP; > >-- prompt Tables and sequences created and populated. >-- set feedback on > > > >------------------------------------------------------------------------ > > >---------------------------(end of broadcast)--------------------------- >TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > >