Help with determination for aggregate functions in SQL statement

[unicornkhh]

Hi everyone,
I'm new here and have searched related topics but non is related to my question:
My company had an old VFP 6.0 but I installed VFP 9.0 on my desktop.
Here's the SQL statement I have in the Excel VBA:
select sh.id, sh.finaldate, sh.writtendate, sum(ar.amount) as 'balance' ;
from sohead sh JOIN ar ON sh.id=ar.so ;
where not deleted() and sh.status='F' and sh.kind=='S' ;
AND sh.finaldate BETWEEN DATE(2005,1,1) AND DATE(2006,9,31) ;GROUP BY sh.id, sh.finaldate, sh.writtendate;

I would like to minimize the selection by adding a filter condition in the WHERE clause like " balance!=0" or "balance<>0" . The statement was tested OK in Ms SQL 2000 but failed in VFP 9.0. The error message is "Column balance not found"
Could anyone shed light on this?

 

[bborissov]

ldStart = DATE(2005,1,1)
ldEnd   = DATE(2005,9,30) && September does not have 31 days :-)
SELECT sh.id, sh.finaldate, 
       sh.writtendate,
       SUM(ar.amount) as 'balance' ;
FROM sohead sh
     JOIN ar ON sh.id=ar.so ;
WHERE  not deleted() and;
       sh.status='F' and;
       sh.kind=='S'  AND;
       sh.finaldate BETWEEN m.ldStart AND m.ldEnd;
GROUP BY sh.id, sh.finaldate, sh.writtendate;
HAVING balance # 0

You can't use newly created column in WHERE clause.
Also, when you have more than one table involved in SELECT using functions like DELETED(), RECNO(), RECCOUN() could cause unpredictable results. That is becuase VFP open tables inlcuded in query again in different aliases and you didn't know against which table that function will be runned. You can't use alias parameter in functions, because as I said VFP opens tables in different aliases.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[jimstarr]

Try HAVING Balance != 0 instead of WHERE.

Jim

 

[unicornkhh]

Hi bborissov and jimstarr ,
having clause did went through.
Thanks a lot!