From jscalia@cityblueprinting.com Tue Nov 2 15:44:44 2004 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id CAE6A3A3D3D for ; Tue, 2 Nov 2004 15:44:44 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 88519-07 for ; Tue, 2 Nov 2004 15:44:39 +0000 (GMT) Received: from mta.algx.net (mta5.algx.net [67.92.168.234]) by svr1.postgresql.org (Postfix) with ESMTP id 17A1B3A3F15 for ; Tue, 2 Nov 2004 15:44:41 +0000 (GMT) Received: from John (ip216-203-212-194.z212-203-216.customer.algx.net [216.203.212.194]) by chimmx04.algx.net (iPlanet Messaging Server 5.2 HotFix 1.16 (built May 14 2003)) with ESMTP id <0I6K008ER6EI0H@chimmx04.algx.net> for pgsql-sql@postgresql.org; Tue, 02 Nov 2004 09:44:42 -0600 (CST) Date: Tue, 02 Nov 2004 10:44:41 -0500 From: "John B. Scalia" Subject: query using a date field that isn't set To: pgsql-sql@postgresql.org Message-id: <0I6K008ES6EI0H@chimmx04.algx.net> MIME-version: 1.0 X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 X-Mailer: Microsoft Office Outlook, Build 11.0.5510 Content-type: multipart/alternative; boundary="Boundary_(ID_YicB2q/LKE21tOW909V8ug)" Thread-index: AcTA8t42xVVO4pWfReqpbwAu4mjm8A== X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=HTML_60_70, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200411/57 X-Sequence-Number: 19376 This is a multi-part message in MIME format. --Boundary_(ID_YicB2q/LKE21tOW909V8ug) Content-type: text/plain; charset=us-ascii Content-transfer-encoding: 7BIT Hi all, I've got a SQL problem that's stumping me. I have an employee table with a field labeled: Emp_terminated DATE Obviously, some employees in this table won't have this field set. I want a query to select only employees without this field being set, ie., only current employees. I've got the inverse working by using: SELECT * FROM employee WHERE ISFINITE(emp_terminated); And that gives me all the old employees (where the field has a value), but for the life of me, I can't figure out the syntax to use for employees that haven't been terminated or quit. Everything I've tried returns no rows. Anybody? -- John --Boundary_(ID_YicB2q/LKE21tOW909V8ug) Content-type: text/html; charset=us-ascii Content-transfer-encoding: 7BIT

Hi all,

I’ve got a SQL problem that’s stumping me. I have an employee table with a field labeled:

Emp_terminated DATE

Obviously, some employees in this table won’t have this field set. I want a query to select only employees without this field being set, ie., only current employees. I’ve got the inverse working by using:

SELECT * FROM employee WHERE ISFINITE(emp_terminated);

And that gives me all the old employees (where the field has a value), but for the life of me, I can’t figure out the syntax to use for employees that haven’t been terminated or quit. Everything I’ve tried returns no rows.

Anybody?

John

--Boundary_(ID_YicB2q/LKE21tOW909V8ug)-- From darcy@druid.net Mon Nov 8 01:56:47 2004 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id EA7153A3E1C for ; Mon, 8 Nov 2004 01:56:47 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 69302-10 for ; Mon, 8 Nov 2004 01:56:35 +0000 (GMT) Received: from druid.net (druid.net [216.126.72.98]) by svr1.postgresql.org (Postfix) with ESMTP id ECC1A3A3CB4 for ; Mon, 8 Nov 2004 01:56:38 +0000 (GMT) Received: from imp.druid.net (imp [216.126.72.111]) by druid.net (Postfix) with SMTP id 2AE0C1C07; Sun, 7 Nov 2004 20:56:44 -0500 (EST) Date: Sun, 7 Nov 2004 20:56:26 -0500 From: "D'Arcy J.M. Cain" To: "John B. Scalia" Cc: pgsql-sql@postgresql.org Subject: Re: query using a date field that isn't set Message-Id: <20041107205626.3db6a856.darcy@druid.net> In-Reply-To: <0I6K008ES6EI0H@chimmx04.algx.net> References: <0I6K008ES6EI0H@chimmx04.algx.net> X-Mailer: Sylpheed version 0.9.12 (GTK+ 1.2.10; i386--netbsdelf) Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/60 X-Sequence-Number: 19379 On Tue, 02 Nov 2004 10:44:41 -0500 "John B. Scalia" wrote: > I've got a SQL problem that's stumping me. I have an employee table > with a field labeled: > > Emp_terminated DATE > > Obviously, some employees in this table won't have this field set. I > want a query to select only employees without this field being set, > ie., only current employees. I've got the inverse working by using: > > SELECT * FROM employee WHERE ISFINITE(emp_terminated); >From http://www.postgresql.org/docs/faqs/FAQ.html: 4.13) In a query, how do I detect if a field is NULL? You test the column with IS NULL and IS NOT NULL. -- D'Arcy J.M. Cain | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.