Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 2DFDF9FB899 for ; Thu, 29 Mar 2007 02:36:21 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 35196-03-8 for ; Thu, 29 Mar 2007 02:36:12 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from weatherbeeta.com.au (218-214-212-153.people.net.au [218.214.212.153]) by postgresql.org (Postfix) with SMTP id 7B3359FB8C4 for ; Thu, 29 Mar 2007 02:36:10 -0300 (ADT) Received: (qmail 22238 invoked from network); 29 Mar 2007 05:36:09 -0000 X-NetworkBox-HamSign: 0101;OUT;wbeetaau1;2e1fd8a61dd8521cbed0ac40a9a3884b; Received: from mail.weatherbeeta.com.au (10.10.0.2) by network-box.weatherbeeta.com.au with SMTP; 29 Mar 2007 05:35:54 -0000 Received: (qmail 23887 invoked by uid 1166); 29 Mar 2007 15:35:52 +1000 Received: from 172.20.0.155 by mail.weatherbeeta.com.au (envelope-from , uid 1165) with qmail-scanner-1.25-st-qms (clamdscan: 0.88.6/2315. perlscan: 1.25-st-qms. Clear:RC:1(172.20.0.155):. Processed in 0.592836 secs); 29 Mar 2007 05:35:52 -0000 X-Antivirus-mail.weatherbeeta.com.au-Mail-From: phillip.smith@weatherbeeta.com.au via mail.weatherbeeta.com.au X-Antivirus-mail.weatherbeeta.com.au: 1.25-st-qms (Clear:RC:1(172.20.0.155):. Processed in 0.592836 secs Process 23881) Received: from it-phil.weatherbeeta.com.au (HELO wbaus090) (172.20.0.155) by mail.weatherbeeta.com.au with SMTP; 29 Mar 2007 15:35:50 +1000 From: "Phillip Smith" To: Subject: SELECT INSTEAD Date: Thu, 29 Mar 2007 15:35:52 +1000 Organization: Weatherbeeta Pty Ptd Message-ID: <00ab01c771c4$1ec9aa50$9b0014ac@wbaus090> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00AC_01C77217.F075BA50" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.6626 Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962 X-Scanned-By-wbeetaau1: Virus scan performed by network-box X-Scanned-By-wbeetaau1: Scanner file id is wbeetaau1-1175146554.277-22129-000 X-Scanned-By-wbeetaau1: No known viruses found in message (received+scanned in 0.05/0.64 secs) X-Scanned-By-wbeetaau1: Spam-Check-Result: No, hits=0 required=7 tests= autolearn=no version=2.0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.522 tagged_above=0 required=5 tests=AWL=-0.612, BAYES_50=0.001, FORGED_RCVD_HELO=0.135, HTML_MESSAGE=0.001, SUBJ_ALL_CAPS=0.997 X-Spam-Level: X-Archive-Number: 200703/342 X-Sequence-Number: 27980 This is a multi-part message in MIME format. ------=_NextPart_000_00AC_01C77217.F075BA50 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hi all, =20 I'm trying to create a view of the query below, but I'm being barked at about "rules on SELECT must have action INSTEAD SELECT". I don't have = any rules in my database, and I don't know how this query is trying to = create one. The query does work if I just run it interactively. Any help would = be much appreciated. =20 SELECT DISTINCT ON (m1.id) UPPER(m1.id) AS id, UPPER(m1.first_name) AS first_name, UPPER(m1.last_name) AS last_name, UPPER(m1.company) AS company, UPPER(m1.address1) AS address1, UPPER(m1.address2) AS address2, UPPER(m1.suburb) AS suburb, UPPER(m1.state) AS state INTO TEMP TABLE mail_duplicates FROM maillist as m1 INNER JOIN maillist as m2 ON ( m1.id <> m2.id AND UPPER(m1.first_name) =3D = UPPER(m2.first_name) AND UPPER(m1.last_name) =3D = UPPER(m2.last_name) AND UPPER(m1.suburb) =3D UPPER(m2.suburb) ) WHERE TRIM(m1.first_name) <> '' AND TRIM(m1.last_name) <> '' ; SELECT d.id AS "ID", INITCAP(d.first_name) AS first_name, INITCAP(d.last_name) AS last_name, INITCAP(d.company) AS company, INITCAP(d.address1) AS address1, INITCAP(d.address2) AS address2, d.suburb AS suburb, d.state AS state FROM mail_duplicates AS d ORDER BY d.last_name, d.first_name, d.suburb, d.id ; =0A*******************Confidentiality and Privilege Notice*******************= =0A=0AThe material contained in this message is privileged and confidential to= =0Athe addressee. If you are not the addressee indicated in this message or= =0Aresponsible for delivery of the message to such person, you may not copy=0Aor= deliver this message to anyone, and you should destroy it and kindly=0Anotify= the sender by reply email.=0A=0AInformation in this message that does not relat= e to the official business=0Aof Weatherbeeta must be treated as neither given = nor endorsed by Weatherbeeta.=0AWeatherbeeta, its employees, contractors or as= sociates shall not be liable=0Afor direct, indirect or consequential loss aris= ing from transmission of this=0Amessage or any attachments=0A ------=_NextPart_000_00AC_01C77217.F075BA50 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi all,

 

I’m trying to create a view of the query = below, but I’m being barked at about “rules on SELECT must have action = INSTEAD SELECT”. I don’t have any rules in my database, and I = don’t know how this query is trying to create one. The query does work if I = just run it interactively. Any help would be much appreciated.

 

SELECT DISTINCT ON = (m1.id)

         = ;               = UPPER(m1.id) AS id,

         = ;               UPPER(m1.first_name) AS first_name,

         = ;               UPPER(m1.last_name) AS last_name,

         = ;               UPPER(m1.company) AS company,

         = ;               UPPER(m1.address1) AS address1,

         = ;               UPPER(m1.address2) AS address2,

         = ;               = UPPER(m1.suburb) AS suburb,

         = ;               UPPER(m1.state) AS state

INTO TEMP TABLE = mail_duplicates

FROM    = maillist as m1

INNER JOIN maillist as m2 = ON

         = ;               (           m1.id = <> m2.id

         = ;               AND      UPPER(m1.first_name) =3D = UPPER(m2.first_name)

         = ;               AND      UPPER(m1.last_name) =3D = UPPER(m2.last_name)

         = ;               AND      UPPER(m1.suburb) =3D = UPPER(m2.suburb)

         = ;               = )

WHERE  = TRIM(m1.first_name) <> ''

 AND   =   TRIM(m1.last_name) <> ''

;

SELECT d.id AS = "ID",

         = ;               INITCAP(d.first_name) AS first_name,

         = ;               INITCAP(d.last_name) AS last_name,

         = ;               INITCAP(d.company) AS company,

         = ;               INITCAP(d.address1) AS address1,

         = ;               INITCAP(d.address2) AS address2,

         = ;               = d.suburb AS suburb,

         = ;               = d.state AS state

FROM    mail_duplicates AS d

ORDER BY d.last_name, = d.first_name, d.suburb, d.id

;


=

=0A*******************Confidentiality and Privilege Notice*************= ******=0A

=0AThe material contained in this message is privileged and conf= idential to=0Athe addressee. If you are not the addressee indicated in this m= essage or=0Aresponsible for delivery of the message to such person, you may no= t copy=0Aor deliver this message to anyone, and you should destroy it and kind= ly=0Anotify the sender by reply email.=0A

=0AInformation in this message that do= es not relate to the official business=0Aof Weatherbeeta must be treated as ne= ither given nor endorsed by Weatherbeeta.=0AWeatherbeeta, its employees, contr= actors or associates shall not be liable=0Afor direct, indirect or consequenti= al loss arising from transmission of this=0Amessage or any attachments=0A
= ------=_NextPart_000_00AC_01C77217.F075BA50--