VFP6.0 Question

[rstum2005]

I Run this Query in VFP6.0 And It Returns the Two Columns but the Radius Column needs to be a whole number.

SELECT DISTINCT RADIUS,count(*)AS QUANTITY FROM rstemp group by RADIUS order by RADIUS

So..

I tried this and it give me an error.

SELECT DISTINCT CAST(RADIUS AS NUMERIC(10,0)),count(*)AS QUANTITY FROM rstemp group by CAST(RADIUS AS NUMERIC(10,0)) order by CAST(RADIUS AS NUMERIC(10,0))

Can anyone help?

 

[Mike Lewis]

CAST() isn't available in VFP 6.0.

Assuming Radius is a numeric data type, use ROUND() to convert it to a whole number:

ROUND(Radius,0) AS Radius

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[rstum2005]

Is The Round Function in VFP9 Also?

Thanks.

 

[rstum2005]

Also, I ran that and it did work but the distinct function stopped working?

 

[Stella740pl]

rstum2005,

Yes, ROUND() function didn't go anywhere, and you don't need DISTINCT keyword when you use GROUP BY clause. Grouping will make it distinct. Also, when you want to group by a field created from a calculated expression, group by field number, not name or expression.

Try this:

SELECT ROUND(RADIUS,0), COUNT(*) AS QUANTITY ;
FROM rstemp ;
GROUP BY 1 ;
ORDER BY 1

 

[rstum2005]

Do you know how to make it so you can run the same query in both SQL and VFP6?


Select ROUND(Radius,0) AS RADIUS, count(*) AS QUANTITY FROM rstemp group by ROUND(Radius,0) order by ROUND(Radius,0)

This runs in SQL but not in VFP.
And The Query you posted runs in VFP but not in SQL.

 

[Stella740pl]

Sorry, I mostly work in VFP.

 

[rstum2005]

Ok Thanks for all your help.

 

[Mike Lewis]

Rstum,

Is The Round Function in VFP9 Also?

Yes, it's in every version since Foxbase and earlier.

Do you know how to make it so you can run the same query in both SQL and VFP6?

What do you mean by that? The SELECT command is is SQL.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[Stella740pl]

Mike, I guess, rstum2005 needs it to run on both, VFP native tables AND SQL Server back end. That's why I said that I mostly work in VFP - meaning native tables.

The way I understood it, with an expression in the GROUP BY clause it doesn't work on VFP native table, and with GROUPing BY the number of the field it doesn't work in SQL Server.

 

[Mike Lewis]

Stella,

I guess, rstum2005 needs it to run on both, VFP native tables AND SQL Server back end.

I didn't notice any reference to SQL Server in particular. But you might be right.

with GROUPing BY the number of the field it doesn't work in SQL Server.

That's correct. You have to specify the name of the field, unlike in VFP. It's caught me out more than once.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)