SQL queries returning different results

[StewartUK]

I've got 2 queries. The first is

SELECT Months.Date, ;
       (SELECT SUM((Months.Date - AllStaff.Started)/36500) ;
          FROM AllStaff ;
          WHERE Months.Date>=AllStaff.Started AND Months.Date<AllStaff.Ended) ;
       AS Centuries ;
  FROM Months ;
INTO CURSOR Totals

Where Months.Date = {^2008-09-01} the figure comes out at 7.6582.

If I run this SQL query:

SELECT {^2008-9-1},SUM(({^2008-9-1}-started)/36500) ;
  FROM allstaff ;
  WHERE {^2008-9-1}>=AllStaff.Started AND {^2008-9-1}<AllStaff.Ended

I get the figure 6.6333

Why am I getting different results?

Thanks,

Stewart

 

[Stella740pl]

What do you expect to get?
I think that your second result might be correct.

I believe that your use of two different aliases in the SUM function of sub-SELECT of your first query (especially when you only mention one in the FROM clause) is what causes your problem. Did you try your sub-query if it works on its own?

Even though it's not exactly what is described here in the quote from HELP, I think it most likely related:

Be careful when using, in join conditions, functions such as DELETED( ), EOF( ), FOUND( ), RECCOUNT( ), and RECNO( ), which support an optional alias or work area. Including an alias or work area in these functions might yield unexpected results. SELECT doesn't use your work areas; it performs the equivalent of  USE ... AGAIN. Single-table queries that use these functions without an optional alias or work area will return proper results. However, multiple-table queries that use these functions — even without an optional alias or work area — might return unexpected results.

 

[StewartUK]

Thanks for your reply Stella,

Yes, having looked at it longer and knowing the situation, I agree that the second result is correct.

In the end I ditched trying the correlated query and used good old SCAN.

Have a good weekend.

Stewart