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(at)entropia(dot)co(dot)uk> wrote: > Date
sent: Mon, 3 Feb 2003 12:59:33 +0000
> (GMT)
> From: Thomas Adam
> <thomas_adam16(at)yahoo(dot)com>
> Subject: [NOVICE] Creation of VIEWS not
> working....
> To: pgsql-general(at)postgresql(dot)org
> Copies to: pgsql-novice(at)postgresql(dot)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