>>>Is there any way where we can have functions in groupby while firing a SQL statement to visual foxpro.
>>>
>>>Eg: select jobno,year(eta) from jobmast group by year(eta)
>>>
>>>The above SQL gives an error. THe roundabout way is
>>>
>>>select jobno,year(eta) as yr from jobmast group by yr
>>>
>>>The above SQL Statement works fine, but the OLAP tool which connects to visual foxpro database generates the 1st SQL statement. And the report dosen't go through.
>>>
>>>Regards
>>>Ravi
>>
>>The group by needs to be one of the fields in the select's field list. The >first one would work if it was group by 2 instead.
>
>Mark,
>
>I am using Business Objects to generate SQL and BO generates the SQL as the first one. I am wondering if vfp can work based on the first method.
>Regards
>Ravi

I know of no software that will run the first SQL statement. That is incorrect SQL syntax in all cases. Most SQL builders are dumb when it come to adding group by and order by clauses. They generally just repeat the field list to pick from when creating these clauses. The problem starts because you lose control of what the resulting field name will be when functions are applied to a selected field unless you specify that field name with the "AS" clause. The builder in VFP will allow you to apply a function then tack on the AS FieldName. You will always be better off with specifying a field name to use, otherwise VFP will generate a name for you. In your example, the generated name would probably be year_eta, which is why you get the error [because the group by field year(eta) does not exist].