Re: SQL select by date

Hi Matt,

If the range were 2004-05-10 thru 2004-10-20 then what would happen to a
date like 2004-06-01?  It should be in the range, but because you're
requiring 01 to be between 10 and 20, it won't be selected.  Assuming you
meant to compare month to months that is:

> SELECT * FROM file WHERE
> POSTCY between FROMYR and TOYR and
> POSTMM between FROMMM and TOMM and
> POSTDD between FROMDD and TODD and

But in any case, it's not necessary to convert the 3 fields to a date field.
Try

SELECT * FROM file
 WHERE digits(POSTCY)||digits(POSTMM)||digits(POSTDD) between FROMDATE and
THRUDATE

run it in debug and see what the optimizer says about your existing indices
and what you should create.

Peter Dow
Dow Software Services, Inc.
909 793-9050 voice
909 793-4480 fax
951 522-3214 cell


----- Original Message ----- 
From: "Tyler, Matt" <mattt@xxxxxxxxxxxxxx>
To: "'Midrange Systems Technical Discussion'" <midrange-l@xxxxxxxxxxxx>
Sent: Friday, September 24, 2004 1:11 PM
Subject: RE: SQL select by date


> Rich,
> How about this...
>
> SELECT * FROM file WHERE
> POSTCY between FROMYR and TOYR and
> POSTMM between FROMDD and TODD and
> POSTDD between FROMDD and TODD and
>
> Where you could make this run in a *QMQRY object and pass all from and to
as
> variables or put into SQLRPGLE.  No need to create a new index or redesign
> the file to hold true dates (the next best option if this will not work
for
> you).
>
> Thank you,
> Matt Tyler
> WinCo Foods, Inc
> mattt@xxxxxxxxxxxxxx
>
> -----Original Message-----
> From: Rich Duzenbury [mailto:rduz-midrange@xxxxxxxxxxxxxxxxxxx]
> Sent: Friday, September 24, 2004 11:41 a
> To: ISeries Mailing List
> Subject: SQL select by date
>
> Hi,
>
> I want to select some records via SQL that contain a transaction posting
> date via a date range.  Unfortunately, the dates are stored in
> components:
>
> POSTCY  4,0
> POSTMM  2,0
> POSTDD  2,0
>
> In order to do a range selection in a where clause, the date fields must
> be concatenated together in some way.
>
> This file has millions of records, and there is an index by POSTYY,
> POSTMM, POSTDD.
>
> What is the correct (efficient) way that will hopefully use the existing
> index?
>
> Thanks.
> --
> Regards,
> Rich
>
> Current Conditions in Des Moines, IA
> Few Clouds
> Temp 71.6F
> Winds out of the North at 6mph
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.768 / Virus Database: 515 - Release Date: 9/22/2004