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

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