Re: Creation of VIEWS not working.... THANKS :-)

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