Re: Creation of VIEWS not working.... - Mailing list pgsql-general

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
>
>