Received: from mail.opentec.com.au ([203.5.51.3]) by hub.org (8.10.1/8.10.1) with ESMTP id e679SuW49059 for ; Fri, 7 Jul 2000 05:28:57 -0400 (EDT) Received: from opentec.com.au (ochre) by mail.opentec.com.au (Sun Internet Mail Server sims.3.5.1999.07.30.00.05.p8) with ESMTP id <0FXB00602MKSSN@mail.opentec.com.au> for pgsql-sql@postgresql.org; Fri, 7 Jul 2000 19:34:04 +1000 (EST) Date: Fri, 07 Jul 2000 19:37:27 +1000 From: Paul McGarry Subject: Re: MAX() of 0 records. To: benoit@cyberdeck.net Cc: pgsql-sql@postgresql.org Message-id: <3965A4D7.C489FCD8@opentec.com.au> MIME-version: 1.0 X-Mailer: Mozilla 4.61 [en] (Win98; I) Content-type: multipart/mixed; boundary="------------7F24A829F2D98BDD72FFDF5F" X-Accept-Language: en References: <200007070832.KAA23787@benoit.dagon.pdt> X-Archive-Number: 200007/76 This is a multi-part message in MIME format. --------------7F24A829F2D98BDD72FFDF5F Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Hi Benoit, > I once had the same problem with an int4 column and solved it by > using the function below : > > CREATE FUNCTION "nulliszero" (int4 ) > RETURNS int4 AS > 'SELECT 0 WHERE $1 IS NULL > UNION SELECT $1 WHERE $1 IS NOT NULL' LANGUAGE 'SQL'; > > Your request then would look like : > (...) > SET entry_maxprice=nulliszero(MAX(item_price)); > (...) Thanks, I'm afraid that doesn't help in this context (see attached sql). I'm sure it will come in handy some time though. The problem isn't that MAX(item_price) returns null, it's that it causes an error. From what I have distilled from the bugs/hackers list where Tom has kindly written a lengthy response/discussion of the problem it seems that in this situation (when no rows match the where condition) the MAX (or min or count or any aggregate function?) would cause an implicit grouping to occur around a null field which the backend doesn't like. I'd read Tom's post if you are interested though, it probably makes more sense! -- Paul McGarry mailto:paulm@opentec.com.au Systems Integrator http://www.opentec.com.au Opentec Pty Ltd http://www.iebusiness.com.au 6 Lyon Park Road Phone: (02) 9878 1744 North Ryde NSW 2113 Fax: (02) 9878 1755 --------------7F24A829F2D98BDD72FFDF5F Content-Type: application/x-unknown-content-type-sql_auto_file; name="benoit.sql" Content-Transfer-Encoding: base64 Content-Disposition: inline; filename="benoit.sql" Q1JFQVRFIEZVTkNUSU9OIHBscGdzcWxfY2FsbF9oYW5kbGVyICgpIFJFVFVS TlMgT1BBUVVFIEFTDQogICAgICAgICAnL29wdC9pZWJ1c2luZXNzL2FwcC9w Z3NxbC9saWIvcGxwZ3NxbC5zbycgTEFOR1VBR0UgJ0MnOw0KDQpDUkVBVEUg VFJVU1RFRCBQUk9DRURVUkFMIExBTkdVQUdFICdwbHBnc3FsJw0KICAgICAg ICAgSEFORExFUiBwbHBnc3FsX2NhbGxfaGFuZGxlcg0KICAgICAgICAgTEFO Q09NUElMRVIgJ1BML3BnU1FMJzsNCg0KDQpDUkVBVEUgVEFCTEUgZW50cnko DQoJZW50cnlfaWQgSU5UNCBQUklNQVJZIEtFWQ0KKTsNCg0KQ1JFQVRFIFRB QkxFIGVudHJ5X3N0YXRzDQooDQoJZW50cnlfaWQgSU5UNCBOT1QgTlVMTCBS RUZFUkVOQ0VTIGVudHJ5IE9OIERFTEVURSBDQVNDQURFLA0KCWVudHJ5X21p bnByaWNlIElOVDQgTk9UIE5VTEwgREVGQVVMVCAwDQopOw0KDQpDUkVBVEUg RlVOQ1RJT04gaW5pdF9lbnRyeV9zdGF0c19mdW5jKCkgUkVUVVJOUyBPUEFR VUUgQVMgJw0KCURFQ0xBUkUNCglCRUdJTg0KCQlJTlNFUlQgSU5UTyBlbnRy eV9zdGF0cyBWQUxVRVMgKE5FVy5lbnRyeV9pZCwwKTsNCgkJUkVUVVJOIE5V TEw7DQoJRU5EOw0KJyBMQU5HVUFHRSAncGxwZ3NxbCc7DQoNCkNSRUFURSBU UklHR0VSIGluaXRfZW50cnlfc3RhdHMgQUZURVIgSU5TRVJUIE9OIGVudHJ5 DQpGT1IgRUFDSCBST1cgRVhFQ1VURSBQUk9DRURVUkUgaW5pdF9lbnRyeV9z dGF0c19mdW5jKGVudHJ5X2lkKTsNCg0KSU5TRVJUIElOVE8gZW50cnkgVkFM VUVTICgxKTsNCg0KQ1JFQVRFIFRBQkxFIGl0ZW0oDQoJaXRlbV9pZCBJTlQ0 IFBSSU1BUlkgS0VZLA0KCWl0ZW1fZW50cnlfaWQgSU5UNCBOT1QgTlVMTCBS RUZFUkVOQ0VTIGVudHJ5IE9OIERFTEVURSBOTyBBQ1RJT04sDQoJaXRlbV9w cmljZSBJTlQ0IE5PVCBOVUxMLA0KCWl0ZW1fbGl2ZSBib29sIE5PVCBOVUxM IERFRkFVTFQgJ24nDQopOw0KDQpDUkVBVEUgRlVOQ1RJT04gIm51bGxpc3pl cm8iIChpbnQ0KQ0KUkVUVVJOUyBpbnQ0IEFTDQonU0VMRUNUIDAgV0hFUkUg JDEgSVMgTlVMTA0KVU5JT04gU0VMRUNUICQxIFdIRVJFICQxIElTIE5PVCBO VUxMJw0KTEFOR1VBR0UgJ1NRTCc7DQoNCkNSRUFURSBGVU5DVElPTiBzZXRl bnRyeXN0YXRzICgpDQpSRVRVUk5TIE9QQVFVRSBBUyAnDQoJREVDTEFSRQ0K CUJFR0lODQoJCUlGIFRHX09QPScnSU5TRVJUJycgVEhFTg0KCQkJVVBEQVRF IGVudHJ5X3N0YXRzIA0KCQkJU0VUIGVudHJ5X21pbnByaWNlPW51bGxpc3pl cm8obWluKGl0ZW1fcHJpY2UpKQ0KCQkJRlJPTSBpdGVtIHdoZXJlIGVudHJ5 X3N0YXRzLmVudHJ5X2lkPWl0ZW1fZW50cnlfaWQgQU5EIGl0ZW1fZW50cnlf aWQ9TkVXLml0ZW1fZW50cnlfaWQgQU5EIGl0ZW1fbGl2ZT0nJ3QnJzsNCgkJ RU5EIElGOw0KCQlSRVRVUk4gTlVMTDsNCglFTkQ7DQonIExBTkdVQUdFICdw bHBnc3FsJzsNCg0KQ1JFQVRFIFRSSUdHRVIgdXBkYXRlZW50cnlwcmljZXMg QUZURVIgSU5TRVJUIE9OIGl0ZW0NCkZPUiBFQUNIIFJPVyBFWEVDVVRFIFBS T0NFRFVSRSBzZXRlbnRyeXN0YXRzKCk7DQoNCklOU0VSVCBJTlRPIGl0ZW0g VkFMVUVTICgxLDEsMTIsJ2YnKTsNCg0K --------------7F24A829F2D98BDD72FFDF5F--