A few months ago I posted the following:
quote
I am selecting a bunch of invoices from a MS SQL Server table. I just want to add the total of all invoices.
In the management studio I do this:
Select sum(total) as total from terbo.dbo.invoices
I get 212649.17
When I do the same calculation in VFP
cCmd = ‘Select sum(total) as total from terbo.dbo.invoices’ sqlexec(thisform.nHandle,cCmd,’curTotals’)
nTotal = curTotals.total
nTotal is 212.65
How can I get the correct amount?
unquote
I would still know the answer to this problem, because the weird thing is that it only happens with the tables of just one database. That is, I have two similar databases, with similar tables. However, the vfp cursor obtained from this particular database table gives the above error where a similar table in another database does not.
In both my Windows 10 and VFP9 SP2 the regional settings are a dot as a thousands separator, a comma as a decimal point and a semicolon as lists separator. These are the Spanish language settings for numbers
The numerical fields in the tables are defined as numerical (18,2) not null with a default value of 0
A select statement such as this:
cCmd = 'select invtotal from mydatabase.dbo.invoices where invid = '+transform(nPK) sqlexec(nHandle,cCmd,'curInvTotal')
brings a cursor where a browse shows 12.256,85 (note the dot shown as a thousands separator. It should show as 12256,85) If I place the mouse over the invtotal column on the browse, the number shows as 12.25
I am really baffled and do not know what to do.
Any leads on where to tweak the database on SQL Server, or configure the software would be appreciated
Any help?
Happy Easter
Rafael Copquin
Rafael Copquin
Have you tried CASTing your sum(total) to the precision you want? Then you're not leaving it up to the query engine to decide.
--
rk
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of rafael copquin Sent: Friday, March 25, 2016 11:01 AM To: profoxtech@leafe.com Subject: problem with calculations with VFP9 and MSSQL Server
A few months ago I posted the following:
quote
I am selecting a bunch of invoices from a MS SQL Server table. I just want to add the total of all invoices.
In the management studio I do this:
Select sum(total) as total from terbo.dbo.invoices
I get 212649.17
When I do the same calculation in VFP
cCmd = ‘Select sum(total) as total from terbo.dbo.invoices’ sqlexec(thisform.nHandle,cCmd,’curTotals’)
nTotal = curTotals.total
nTotal is 212.65
How can I get the correct amount?
unquote
I would still know the answer to this problem, because the weird thing is that it only happens with the tables of just one database. That is, I have two similar databases, with similar tables. However, the vfp cursor obtained from this particular database table gives the above error where a similar table in another database does not.
In both my Windows 10 and VFP9 SP2 the regional settings are a dot as a thousands separator, a comma as a decimal point and a semicolon as lists separator. These are the Spanish language settings for numbers
The numerical fields in the tables are defined as numerical (18,2) not null with a default value of 0
A select statement such as this:
cCmd = 'select invtotal from mydatabase.dbo.invoices where invid = '+transform(nPK) sqlexec(nHandle,cCmd,'curInvTotal')
brings a cursor where a browse shows 12.256,85 (note the dot shown as a thousands separator. It should show as 12256,85) If I place the mouse over the invtotal column on the browse, the number shows as 12.25
I am really baffled and do not know what to do.
Any leads on where to tweak the database on SQL Server, or configure the software would be appreciated
Any help?
Happy Easter
Rafael Copquin
Rafael Copquin
_______________________________________________ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/56F55299.1080207@fibertel.com.ar ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious. Report [OT] Abuse: http://leafe.com/reportAbuse/56F55299.1080207@fibertel.com.ar
Have you taken a look at the structure (LIST STRU) of the resultant cursor when you get the incorrect result? Perhaps there's a clue there.
On Fri, Mar 25, 2016 at 11:00 AM, rafael copquin rcopquin@fibertel.com.ar wrote:
A few months ago I posted the following:
quote
I am selecting a bunch of invoices from a MS SQL Server table. I just want to add the total of all invoices. In the management studio I do this: Select sum(total) as total from terbo.dbo.invoices I get 212649.17 When I do the same calculation in VFP cCmd = ‘Select sum(total) as total from terbo.dbo.invoices’ sqlexec(thisform.nHandle,cCmd,’curTotals’) nTotal = curTotals.total nTotal is 212.65 How can I get the correct amount? unquote
I would still know the answer to this problem, because the weird thing is that it only happens with the tables of just one database. That is, I have two similar databases, with similar tables. However, the vfp cursor obtained from this particular database table gives the above error where a similar table in another database does not.
In both my Windows 10 and VFP9 SP2 the regional settings are a dot as a thousands separator, a comma as a decimal point and a semicolon as lists separator. These are the Spanish language settings for numbers
The numerical fields in the tables are defined as numerical (18,2) not null with a default value of 0
A select statement such as this:
cCmd = 'select invtotal from mydatabase.dbo.invoices where invid = '+transform(nPK) sqlexec(nHandle,cCmd,'curInvTotal')
brings a cursor where a browse shows 12.256,85 (note the dot shown as a thousands separator. It should show as 12256,85) If I place the mouse over the invtotal column on the browse, the number shows as 12.25
I am really baffled and do not know what to do.
Any leads on where to tweak the database on SQL Server, or configure the software would be appreciated
Any help?
Happy Easter
Rafael Copquin
Rafael Copquin
[excessive quoting removed by server]
Both Ted and Richard, thank you for replying.
However, the resulting cursor has the fields as numeric.
In the case of the table belonging to the database that behaves normally, the sql column is numeric(10,2) and vfp brings it as a numeric(12,2) field.
In the case of the one that behaves crazily, the sql column is numeric(18,2) and vfp brings it as numeric(20,2)
If I browse the first cursor (both are saved to disk to deal with after the queries) it shows the amount in the invtotal column as 12345.67 which is correct
The weird one, even though it is showing the structure as numeric(20,2) shows as 12.345,67 and when the cursor is on the record it shows as 123.45, which is completely wrong. Note the dot separating the thousands, which does not appear in the first case.
I solved the issue temporarily by casting the fields as character in the select from the sql server and the resulting cursor is then CASTed to numeric(12,2).
However, I want to know why the behavior is so wrong in the second case. Because I can't trust the queries if I do not tweak them as described.
El 25/03/2016 a las 12:40, Ted Roche escribió:
Have you taken a look at the structure (LIST STRU) of the resultant cursor when you get the incorrect result? Perhaps there's a clue there.
On Fri, Mar 25, 2016 at 11:00 AM, rafael copquin rcopquin@fibertel.com.ar wrote:
A few months ago I posted the following:
quote
I am selecting a bunch of invoices from a MS SQL Server table. I just want to add the total of all invoices. In the management studio I do this: Select sum(total) as total from terbo.dbo.invoices I get 212649.17 When I do the same calculation in VFP cCmd = ‘Select sum(total) as total from terbo.dbo.invoices’ sqlexec(thisform.nHandle,cCmd,’curTotals’) nTotal = curTotals.total nTotal is 212.65 How can I get the correct amount? unquote
I would still know the answer to this problem, because the weird thing is that it only happens with the tables of just one database. That is, I have two similar databases, with similar tables. However, the vfp cursor obtained from this particular database table gives the above error where a similar table in another database does not.
In both my Windows 10 and VFP9 SP2 the regional settings are a dot as a thousands separator, a comma as a decimal point and a semicolon as lists separator. These are the Spanish language settings for numbers
The numerical fields in the tables are defined as numerical (18,2) not null with a default value of 0
A select statement such as this:
cCmd = 'select invtotal from mydatabase.dbo.invoices where invid = '+transform(nPK) sqlexec(nHandle,cCmd,'curInvTotal')
brings a cursor where a browse shows 12.256,85 (note the dot shown as a thousands separator. It should show as 12256,85) If I place the mouse over the invtotal column on the browse, the number shows as 12.25
I am really baffled and do not know what to do.
Any leads on where to tweak the database on SQL Server, or configure the software would be appreciated
Any help?
Happy Easter
Rafael Copquin
Rafael Copquin
[excessive quoting removed by server]
I don't know if this is relevant but the VFP Help file states that the maximum digits for a numeric field is 15.
Laurie
On 25 March 2016 at 19:35, rafael copquin rcopquin@fibertel.com.ar wrote:
Both Ted and Richard, thank you for replying.
However, the resulting cursor has the fields as numeric.
In the case of the table belonging to the database that behaves normally, the sql column is numeric(10,2) and vfp brings it as a numeric(12,2) field.
In the case of the one that behaves crazily, the sql column is numeric(18,2) and vfp brings it as numeric(20,2)
If I browse the first cursor (both are saved to disk to deal with after the queries) it shows the amount in the invtotal column as 12345.67 which is correct
The weird one, even though it is showing the structure as numeric(20,2) shows as 12.345,67 and when the cursor is on the record it shows as 123.45, which is completely wrong. Note the dot separating the thousands, which does not appear in the first case.
I solved the issue temporarily by casting the fields as character in the select from the sql server and the resulting cursor is then CASTed to numeric(12,2).
However, I want to know why the behavior is so wrong in the second case. Because I can't trust the queries if I do not tweak them as described.
El 25/03/2016 a las 12:40, Ted Roche escribió:
Have you taken a look at the structure (LIST STRU) of the resultant cursor when you get the incorrect result? Perhaps there's a clue there.
On Fri, Mar 25, 2016 at 11:00 AM, rafael copquin rcopquin@fibertel.com.ar wrote:
A few months ago I posted the following:
quote
I am selecting a bunch of invoices from a MS SQL Server table. I just want to add the total of all invoices. In the management studio I do this: Select sum(total) as total from terbo.dbo.invoices I get 212649.17 When I do the same calculation in VFP cCmd = ‘Select sum(total) as total from terbo.dbo.invoices’ sqlexec(thisform.nHandle,cCmd,’curTotals’) nTotal = curTotals.total nTotal is 212.65 How can I get the correct amount? unquote
I would still know the answer to this problem, because the weird thing is that it only happens with the tables of just one database. That is, I have two similar databases, with similar tables. However, the vfp cursor obtained from this particular database table gives the above error where a similar table in another database does not.
In both my Windows 10 and VFP9 SP2 the regional settings are a dot as a thousands separator, a comma as a decimal point and a semicolon as lists separator. These are the Spanish language settings for numbers
The numerical fields in the tables are defined as numerical (18,2) not null with a default value of 0
A select statement such as this:
cCmd = 'select invtotal from mydatabase.dbo.invoices where invid = '+transform(nPK) sqlexec(nHandle,cCmd,'curInvTotal')
brings a cursor where a browse shows 12.256,85 (note the dot shown as a thousands separator. It should show as 12256,85) If I place the mouse over the invtotal column on the browse, the number shows as 12.25
I am really baffled and do not know what to do.
Any leads on where to tweak the database on SQL Server, or configure the software would be appreciated
Any help?
Happy Easter
Rafael Copquin
Rafael Copquin
[excessive quoting removed by server]
On Fri, Mar 25, 2016 at 3:35 PM, rafael copquin rcopquin@fibertel.com.ar wrote:
Both Ted and Richard, thank you for replying.
However, the resulting cursor has the fields as numeric.
In the case of the table belonging to the database that behaves normally, the sql column is numeric(10,2) and vfp brings it as a numeric(12,2) field.
In the case of the one that behaves crazily, the sql column is numeric(18,2) and vfp brings it as numeric(20,2)
If I browse the first cursor (both are saved to disk to deal with after the queries) it shows the amount in the invtotal column as 12345.67 which is correct
The weird one, even though it is showing the structure as numeric(20,2) shows as 12.345,67 and when the cursor is on the record it shows as 123.45, which is completely wrong. Note the dot separating the thousands, which does not appear in the first case.
I solved the issue temporarily by casting the fields as character in the select from the sql server and the resulting cursor is then CASTed to numeric(12,2).
I suspect Laurie's on to something, and that your getting the misplaced decimal place because the size of the numeric field exceeds the maximum width.
What if you were to cast the result as a numeric N(15,2) or smaller?
-
Laurie Alvey -
rafael copquin -
Richard Kaye -
Ted Roche