RE: SQL select by date

I think you're right.  It IS that much trouble with where clauses.  Order 
by clauses are more forgiving.  If you don't use their suggestion of the 
WITH clause then you can redefine the field in the where clause.
SELECT  date(varchar(POSTCY) ||'-'||varchar(postmm)||'-'||
             varchar(POSTDD)) AS POSTDATE, 
             POSTCY, POSTMM, POSTDD 
FROM ROB/RICHD 
where   date(varchar(POSTCY) ||'-'||varchar(postmm)||'-'||
             varchar(POSTDD)) 
between  date('2004-09-09') and date('2005-01-01') 

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 03:40 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






It seems like the main problem is accessing the POSTDATE calculated
field in the WHERE clause:

Select 
         digits(postcy) || digits(postmm) || digits(postdd) as postdate
from 
         tran
where 
         postdate > '20040101'

fails:
Column POSTDATE not in specified tables

Anyone know what's up with that? 

I found the following kludgy thing in the SQL reference manual, which
works, but it seems *wrong* to have to go to this much trouble to use a
calculated field in a where clause.  I *must* be doing something
incorrectly.

with
    tr (account_num, postdate) as
    ( select account_num, 
                digits(postcy) || 
                digits(postmm) ||
                digits(postdd from tran)
select
    tr.account_num
from 
    tr
where
    tr.postdate >= :$from_date and
    tr.postdate <= :$thru_date

Any ideas?

Thanks.

On Fri, 2004-09-24 at 14:48, rob@xxxxxxxxx wrote:
> 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
--
Regards,
Rich

Current Conditions in Des Moines, IA
Few Clouds
Temp 75.2F

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