RE: SQL select by date

[Rich Duzenbury <rduz-midrange@xxxxxxxxxxxxxxxxxxx>]

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