× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.

As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.