Re: Dumb question--between/and

->Date: 13 Sep 1994 17:53:02 -0600 ->From: "Holly Gordon" <holly_gordon@tsl.texas.gov> ->Subject: Dumb question--between/and ->To: informix-list@rmy.emory.edu -> ->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 -> ->Thanks--must be in the manual somewhere... ->----------------------------------------------------------------------------- ->Holly Gordon (hgordon@tsl.texas.gov) | SW--CLSI LIBS100+, Informix Online 4.10 ->Texas State Library | OS--UNIX system V, release 3.2.0 i386 ->Austin, Texas | HW--Sequent S27 (386) I don't have my MARC documents handy, but I will assume that creation_date is a character field. If it is a DATE or DATETIME field, then I have no idea how the * in the limits will affect the query. That said, I would recast your query this way: select author from marc where title_id in (select title_id from item where agency_id = "TNR") and creation_date between "19930901" and "19940831235959" I did the subselect first on the assumption that it will return fewer rows than the date range on the entire marc table; obviously this might not be true for your data. I replaced the wildcard * in the limits of between; this is what I think is the source of your problem. Regards, Alan ___________________________ ______________________| R. Alan Popiel |__________________________ \\ Internet: | Martin Marietta, SLS | / \\ alan@den.mmc.com | P.O. Box 179, M/S 3810 | Std disclaimers apply. / )Voice: | Denver, CO 80201-0179 USA | ( / 303-977-9998 |___________________________| (But you knew that!) \\ /________________________) (____________________________\\