PDA

View Full Version : Access SQL



28-04-2000, 03:32 PM
In Access SQL, it seems that you can
refer to a variable created in the same
select statement

e.g. creating a new
variable called age, then refering to it

SELECT INT((admit_date-birth_date)/365.25) as AGE,
iif(AGE<16,'Child','Adult') as agegp

or even refering to it before defining it

SELECT iif(AGE<16,'Child','Adult') as agegp,
INT((admit_date-birth_date)/365.25) as AGE

So I assume that these select statements are
not performed sequentially. Access seems to
be unusually clever in this way, with other
programs I have used you have to define
a variable first.

Yet when you refer to newly a defined variable
in a where statement

e.g.

SELECT INT((admit_date-birth_date)/365.25) as AGE,
iif(AGE<16,'Child','Adult') as agegp
from data
where AGE<65

it doesn't like it. Have to use original definition

SELECT INT((admit_date-birth_date)/365.25) as AGE,
iif(AGE<16,'Child','Adult') as agegp
from data
where INT((admit_date-birth_date)/365.25)<65

In the books I have been able to get hold of, I
have not been able to find any explanation of this,
and of how Access SQL performs such queries.
Can anyone help me here?