RE: SQL select by date

[Rich Duzenbury <rduz-midrange@xxxxxxxxxxxxxxxxxxx>]

On Fri, 2004-09-24 at 12:56, Ken Slaugh wrote:
> Try...
> 
> DATE(VARCHAR(POSTMM) || '/' || VARCHAR(POSTDD) || '/' || VARCHAR(POSTCY)) AS
> POSTDATE 
> 
> ... You should be able to refer to POSTDATE in the WHERE clause and/or ORDER
> BY.
> 
This doesn't seem to work.  If I try to reference the field in the where
clause, I get a:
SQL0206 - column POSTDATE not in specified tables.


If I try to move this into the where clause as 

where DATE(VARCHAR(POSTCY) || '/' || VARCHAR(POSTMM) || '/' ||
VARCHAR(POSTDD)) > date('2004/1/1')

Then I receive a CPD4019, Select or omit on field *N member TRAN, reason
code 1:
1, The date was not valid for a decimal field

> HTH, Ken
> 
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Rich Duzenbury
> Sent: Friday, September 24, 2004 10:41 AM
> 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