RE: SQL select by date -- MIDRANGE-L

That would be an idea.  An alternative would have been to create a view 
that uses this date field
CREATE VIEW ROB/RICHDVIEW 
 AS SELECT 
      date(varchar(POSTCY)|| '-' || 
           varchar(POSTMM) ||'-'|| 
           varchar(POSTDD)) 
    as POSTDATE, 
    POSTCY, POSTMM, POSTDD FROM rob/richd

Then you could do things like
select * from rob/richdview 
where postdate between '2001-01-01' and '2005-12-31'

Rob Berendt
-- 
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





Ahmed Yusuf <ayusuf@xxxxxxxxxxxxxxxxxx> 
Sent by: midrange-l-bounces@xxxxxxxxxxxx
09/25/2004 01:35 AM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
cc

Fax to

Subject
RE: SQL select by date






Hi Duzenburry,

If I were you, would have used the following command to get the date 
field,
and would have created a temporary file.

Once the file is created you can retrieve any kind of information.

 

Regards

Ahmed Hussain


-----Original Message-----
From: rob@xxxxxxxxx [mailto:rob@xxxxxxxxx] 
Sent: Friday, September 24, 2004 10:48 PM
To: Midrange Systems Technical Discussion
Subject: RE: SQL select by date

For all records, or just a few?

SELECT  date(varchar(POSTCY) ||'-'||varchar(postmm)||'-'||
             varchar(POSTDD)) AS POSTDATE, 
             POSTCY, POSTMM, POSTDD 
FROM ROB/RICHD 
....+....1....+....2....+....3....+
POSTDATE    POSTCY   POSTMM  POSTDD
2004-12-31   2,004     12      31 
2004-01-31   2,004      1      31 
2002-01-31   2,002      1      31 
********  End of data  ******** 
F13=Services
1. Change session attributes
Date format  . . . . . . . . .   *ISO


Rob Berendt
-- 
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





Rich Duzenbury <rduz-midrange@xxxxxxxxxxxxxxxxxxx> 
Sent by: midrange-l-bounces@xxxxxxxxxxxx
09/24/2004 01:13 PM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
cc

Fax to

Subject
RE: SQL select by date






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

--
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.
--
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.