The answer to Dumb question--between/and
Hey netters--sorry, I left out an important element of this problem--for some
reason, though creation_date is obviously a date, it is in the database as a
25 character char variable. That makes a big difference, best I can tell. I
think the answer to the problem is:
select author from marcrept
where title_id in (select title_id from item where agency_id = "TNR")
and creation_date between "19930901" and "19940831235959"
end
Many thanks for everyone's help!!! And for not openly laughing...
_______________________________________________________________________________
>To: tsl.texas.gov!holly_gordon@uunet.uu.net
>Cc: Informix Net
>From: Robert Minter on Wed, Sep 14, 1994 3:22 PM
>Subject: Re: Dumb question--between/and
>RFC Header:Received: by Delicious with SMTP;14 Sep 1994 15:22:08 -0600
>Received: from rmy.rmy.emory.edu by delta1 (5.65/2.4)
> id AA20260; Wed, 14 Sep 94 15:24:33 -0500
>Received: by
> rmy.rmy.emory.edu (5.65/Emory_rmy.3.4.0) via MAILPROG
> id AA28858 ; Wed, 14 Sep 94 16:26:49 -0400
>Return-Path: ilist@rmy.emory.edu
>Subject: Re: Dumb question--between/and
>To: tsl.texas.gov!holly_gordon@uunet.uu.net
>Date: Wed, 14 Sep 1994 08:32:34 -0700 (PDT)
>From: Robert Minter <rob@dssmktg.com>
>Cc: Informix Net <rmy.emory.edu!informix-list@uunet.uu.net>
>In-Reply-To: <n1432641859.93866@Delicious> from "Holly Gordon" at Sep 13, 94
>05:53:02 pm
>X-Mailer: ELM [version 2.4 PL2]
>Content-Type: text
>Content-Length: 1041
>Message-Id: <9409140832.aa13181@dssmktg.com>
>Sender: informix-list-owner@rmy.emory.edu
>Reply-To: Robert Minter <rob@dssmktg.com>
>X-Informix-List-To: hgordon@tsl.texas.gov
>X-Informix-List-Id: <list.4702>
From Holly Gordon:
*
* Can someone tell me what is wrong with this between clause? Seems to cause
a
* long transaction, which aborts--
*
* Select author from marc
* where creation_date between "19930901*" and "19940831*" and
* title_id in (select title_id from item where agency_id = "TNR")
* end
You cannot have wildcard matching characters within a date field match.
The '*' and '?' wildcards are only used in CHAR fields.
(See manual INFORMIX-4GL Users Guide Version 4.00 Page 12-10 Part No.
000-7043)
Change to:
Select author from marc
where creation_date between "19930901" and "19940831" and
title_id in (select title_id from item where agency_id = "TNR")
end
Robert Minter |Data Systems Support| \\\\\\_///
Programmer, Software Development | Orange, CA | ( _ _ )
internet: rob@dssmktg.com | Tel: 714.771.0454 | (| ^ |)
bangpath: uunet.uu.net!dssmktg!rob| Fax: 714.771.3028 | \\`-'/
#include <disclaimer.h> SURF'S UP \\_/