RE: SQL select by date -- MIDRANGE-L

The reason the WHERE clause cannot use an alias from the column list is, the WHERE clause is applied early, before the result set is built, and it is the result set that has the "AKA" column name. The WHERE clause goes against the original column names only.

Now the ORDER BY is applied to the result set - it knows the "AKA" name, and it might not know the original column names, if they are not in the column list.

I think this is documented somewhere - don't remember.

HTH
Vern

At 03:40 PM 9/24/2004, you wrote:

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