RE: SQL select by date

["Goodbar, Loyd (ETS - Water Valley)" <LGoodbar@xxxxxxxxxxxxxx>]

No ideas, but I've had to use the same calculations in WHERE and ORDER BY
clauses when I wanted them, i.e.

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

Not an elegant solution to be sure.


Loyd Goodbar
Senior programmer/analyst
BorgWarner
E/TS Water Valley
662-473-5713

-----Original Message-----
From: Rich Duzenbury [mailto:rduz-midrange@xxxxxxxxxxxxxxxxxxx] 
Sent: Friday, September 24, 2004 15:41
To: Midrange Systems Technical Discussion
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.