RE: SQL select by date

[Rich Duzenbury <rduz-midrange@xxxxxxxxxxxxxxxxxxx>]

Elvis, I need to select by date RANGE.  POSTCY, POSTMM and POSTDD are
numeric fields in the table.

On Fri, 2004-09-24 at 13:01, Elvis Budimlic wrote:
> Avoid concatenation, as query optimizer does not like to use indexes with
> derived fields.
> Perhaps you could do something like 
> WHERE POSTCY = '2004' AND POSTMM = '11' AND POSTDD = '27'?
> 
> That should use the existing index.  Or perhaps build new 3 field index.
> STRDBG before you run it and see what query optimizer suggests.
> 
> Elvis
> 
> 
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Rich Duzenbury
> Sent: Friday, September 24, 2004 12:41 PM
> 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.
--
Regards,
Rich

Current Conditions in Des Moines, IA
Few Clouds
Temp 71.6F
Winds out of the North at 6mph