>>>Hi,
>>>
>>>The SQL Select I have that works with VFP does not work with SQL Server database. The SQL statement has HAVING clause. For example (simplified) the following does not work with SQL Server database:
>>>
>>>
>>>select COUND(DISTINCT(WO_NUMBER)) AS TOT_NUM from MyTable WHERE <SomeCondition> HAVING TOT_NUM > 5
>>>
>>>
>>>The error is Invalid Column name TOT_NUM. How would I change the above SQL Select make the HAVING working?
>>>TIA.
>>
>>
>>select COUNT(DISTINCT WO_NUMBER) AS TOT_NUM
>> from MyTable
>>WHERE <SomeCondition>
>>HAVING COUNT(DISTINCT WO_NUMBER) > 5
>>
>
>What I didn't write in my initial message and that probably makes a difference is that I have GROUP BY clause. And therefore, the TOT_NUM is a grouped value. So I am not sure if the above will apply.
>Thank you.
HAVING is meant to be used with Group by - that's why you use it. Otherwise it would be just part of the where expression. Just try it. Or, post your entire sql statement. But the answer to your question is that in T SQL you can't refer to the expression by its alias but need to recreate the entire expression of the Group by in the Having clause.
Charles Hankey
Though a good deal is too strange to be believed, nothing is too strange to have happened.
- Thomas Hardy
Half the harm that is done in this world is due to people who want to feel important. They don't mean to do harm-- but the harm does not interest them. Or they do not see it, or they justify it because they are absorbed in the endless struggle to think well of themselves.
-- T. S. Eliot
Democracy is two wolves and a sheep voting on what to have for lunch.
Liberty is a well-armed sheep contesting the vote.
- Ben Franklin
Pardon him, Theodotus. He is a barbarian, and thinks that the customs of his tribe and island are the laws of nature.
