DATE/ALIAS issue

brucezepplin · #1 (**permalink**) 08-10-11, 05:02

Hi everyone, I am having a problem including aliased dates in as part of the criteria for a JOIN clause. I have:

Code:

SELECT * FROM

(SELECT DISTINCT PERSON,  DATE AS DATE1
FROM DB1
) A

INNER JOIN

(SELECT DISTINCT PERSON, DATE AS DATE2
FROM DB2
) B

ON A.PERSON = B.PERSON
AND DATE1 BETWEEN (DATE2 - 1 YEAR) AND DATE2

and it's the last line that's giving me the problem. I am being told that DATE1 and DATE2 are both invalid in the context they are used. Any know how to solve this? accessing them as A.DATE gives me the same problem also. So it doesn't sound like an alias issue.

Thanks!