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

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