Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2011
    Posts
    33

    Unanswered: DATE/ALIAS issue

    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!

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    what is the data type for DATE column in DB1 and DB2 tables ?
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jul 2011
    Posts
    33

    reply

    they are both of type DATE.

  4. #4
    Join Date
    Jul 2011
    Posts
    33

    Solved

    I needed to put the Person in a GROUP BY clause within their own sub-select clause. Problem solved.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •