Hi all
It's probably best if I explain this by example. Let's say we have a field in VFP with Width 4 and Decimal 2. In a browse the maximum value that we can enter is 9.99 (as would be expected). BUT by means of a REPLACE we could end up with values of 99.9, 999 or even 9999 without getting numeric overflow.
Because of this, and because we want to write away the VFP data to SQL Server we have been setting the equivalent SQL Server field (or more strictly column) to a Precision of 6 and Scale 2. Similarly a VFP field of Width 8 and Decimal 3 would have an equivalent SQL Server column of Precision 11 and Scale 3.
This isn't really very satisfactory and I was wondering how other people deal with this. Ideally our code should not allow a replace with values (in the first example) greater than 9.99. Of course VFP does complain if we try to replace the value with 99999 (Numeric overflow. Data was lost)
We would have preferred it if VFP were to complain in ALL cases where the replacement value is greater than 9.99 but, alas, that is not the case.
Any thoughts, comments or suggestions would be very welcome.
Paul Newton
Have you thought about creating a field validation rule say field1 <= 9.99?
Laurie
On 8 August 2016 at 10:47, Paul Newton Paul.Newton@pegasus.co.uk wrote:
Hi all
It's probably best if I explain this by example. Let's say we have a field in VFP with Width 4 and Decimal 2. In a browse the maximum value that we can enter is 9.99 (as would be expected). BUT by means of a REPLACE we could end up with values of 99.9, 999 or even 9999 without getting numeric overflow.
Because of this, and because we want to write away the VFP data to SQL Server we have been setting the equivalent SQL Server field (or more strictly column) to a Precision of 6 and Scale 2. Similarly a VFP field of Width 8 and Decimal 3 would have an equivalent SQL Server column of Precision 11 and Scale 3.
This isn't really very satisfactory and I was wondering how other people deal with this. Ideally our code should not allow a replace with values (in the first example) greater than 9.99. Of course VFP does complain if we try to replace the value with 99999 (Numeric overflow. Data was lost)
We would have preferred it if VFP were to complain in ALL cases where the replacement value is greater than 9.99 but, alas, that is not the case.
Any thoughts, comments or suggestions would be very welcome.
Paul Newton
[excessive quoting removed by server]
Hi Laurie
I am playing around with that just now but we have hundreds of tables with thousands of numeric fields so it would be quite a task (but I could probably automate the process)
Thanks for the suggestion
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Laurie Alvey Sent: 08 August 2016 11:02 To: profoxtech@leafe.com Subject: Re: Width and Decimal (VFP) vs Precision and Scale (SQL)
Have you thought about creating a field validation rule say field1 <= 9.99?
Laurie
On 8 August 2016 at 10:47, Paul Newton Paul.Newton@pegasus.co.uk wrote:
Hi all
It's probably best if I explain this by example. Let's say we have a field in VFP with Width 4 and Decimal 2. In a browse the maximum value that we can enter is 9.99 (as would be expected). BUT by means of a REPLACE we could end up with values of 99.9, 999 or even 9999 without getting numeric overflow.
Because of this, and because we want to write away the VFP data to SQL Server we have been setting the equivalent SQL Server field (or more strictly column) to a Precision of 6 and Scale 2. Similarly a VFP field of Width 8 and Decimal 3 would have an equivalent SQL Server column of Precision 11 and Scale 3.
This isn't really very satisfactory and I was wondering how other people deal with this. Ideally our code should not allow a replace with values (in the first example) greater than 9.99. Of course VFP does complain if we try to replace the value with 99999 (Numeric overflow. Data was lost)
We would have preferred it if VFP were to complain in ALL cases where the replacement value is greater than 9.99 but, alas, that is not the case.
Any thoughts, comments or suggestions would be very welcome.
Paul Newton
[excessive quoting removed by server]
In your example maybe you'd just have to use type Decimal in SQL Server with precision = 6 and scale = 2.
So values of 9999.00 up to 9999.99 would still be valid.
My pleasure. I agree it would be quite a task. I guess you would have to write thousands of ALTER TABLE ALTER COLUMN SET CHECK = commands.
Laurie
On 8 August 2016 at 11:47, Alan Bourke alanpbourke@fastmail.fm wrote:
In your example maybe you'd just have to use type Decimal in SQL Server with precision = 6 and scale = 2.
So values of 9999.00 up to 9999.99 would still be valid.
-- Alan Bourke alanpbourke (at) fastmail (dot) fm
On Mon, 8 Aug 2016, at 11:18 AM, Paul Newton wrote:
Hi Laurie
I am playing around with that just now but we have hundreds of tables with thousands of numeric fields so it would be quite a task (but I could probably automate the process)
Thanks for the suggestion
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Laurie Alvey Sent: 08 August 2016 11:02 To: profoxtech@leafe.com Subject: Re: Width and Decimal (VFP) vs Precision and Scale (SQL)
Have you thought about creating a field validation rule say field1 <= 9.99?
Laurie
On 8 August 2016 at 10:47, Paul Newton Paul.Newton@pegasus.co.uk
wrote:
Hi all
It's probably best if I explain this by example. Let's say we have a field in VFP with Width 4 and Decimal 2. In a browse the maximum value that we can enter is 9.99 (as would be expected). BUT by means of a REPLACE we could end up with values of 99.9, 999 or even 9999 without getting numeric overflow.
Because of this, and because we want to write away the VFP data to SQL Server we have been setting the equivalent SQL Server field (or more strictly column) to a Precision of 6 and Scale 2. Similarly a VFP field of Width 8 and Decimal 3 would have an equivalent SQL Server column of Precision 11 and Scale 3.
This isn't really very satisfactory and I was wondering how other people deal with this. Ideally our code should not allow a replace with values (in the first example) greater than 9.99. Of course VFP does complain if we try to replace the value with 99999 (Numeric overflow. Data was lost)
We would have preferred it if VFP were to complain in ALL cases where the replacement value is greater than 9.99 but, alas, that is not the
case.
Any thoughts, comments or suggestions would be very welcome.
Paul Newton
[excessive quoting removed by server]
Yes but I could do it programmatically thus automating the process of creating the rules
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Laurie Alvey Sent: 08 August 2016 11:56 To: profoxtech@leafe.com Subject: Re: Width and Decimal (VFP) vs Precision and Scale (SQL)
My pleasure. I agree it would be quite a task. I guess you would have to write thousands of ALTER TABLE ALTER COLUMN SET CHECK = commands.
Laurie
On 8 August 2016 at 11:47, Alan Bourke alanpbourke@fastmail.fm wrote:
In your example maybe you'd just have to use type Decimal in SQL Server with precision = 6 and scale = 2.
So values of 9999.00 up to 9999.99 would still be valid.
-- Alan Bourke alanpbourke (at) fastmail (dot) fm
On Mon, 8 Aug 2016, at 11:18 AM, Paul Newton wrote:
Hi Laurie
I am playing around with that just now but we have hundreds of tables with thousands of numeric fields so it would be quite a task (but I could probably automate the process)
Thanks for the suggestion
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Laurie Alvey Sent: 08 August 2016 11:02 To: profoxtech@leafe.com Subject: Re: Width and Decimal (VFP) vs Precision and Scale (SQL)
Have you thought about creating a field validation rule say field1 <= 9.99?
Laurie
On 8 August 2016 at 10:47, Paul Newton Paul.Newton@pegasus.co.uk
wrote:
Hi all
It's probably best if I explain this by example. Let's say we have a field in VFP with Width 4 and Decimal 2. In a browse the maximum value that we can enter is 9.99 (as would be expected). BUT by means of a REPLACE we could end up with values of 99.9, 999 or even 9999 without getting numeric overflow.
Because of this, and because we want to write away the VFP data to SQL Server we have been setting the equivalent SQL Server field (or more strictly column) to a Precision of 6 and Scale 2. Similarly a VFP field of Width 8 and Decimal 3 would have an equivalent SQL Server column of Precision 11 and Scale 3.
This isn't really very satisfactory and I was wondering how other people deal with this. Ideally our code should not allow a replace with values (in the first example) greater than 9.99. Of course VFP does complain if we try to replace the value with 99999 (Numeric overflow. Data was lost)
We would have preferred it if VFP were to complain in ALL cases where the replacement value is greater than 9.99 but, alas, that is not the
case.
Any thoughts, comments or suggestions would be very welcome.
Paul Newton
[excessive quoting removed by server]
That's what I meant - maybe wrapped in a UDF.
Laurie
On 8 August 2016 at 11:58, Paul Newton Paul.Newton@pegasus.co.uk wrote:
Yes but I could do it programmatically thus automating the process of creating the rules
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Laurie Alvey Sent: 08 August 2016 11:56 To: profoxtech@leafe.com Subject: Re: Width and Decimal (VFP) vs Precision and Scale (SQL)
My pleasure. I agree it would be quite a task. I guess you would have to write thousands of ALTER TABLE ALTER COLUMN SET CHECK = commands.
Laurie
On 8 August 2016 at 11:47, Alan Bourke alanpbourke@fastmail.fm wrote:
In your example maybe you'd just have to use type Decimal in SQL Server with precision = 6 and scale = 2.
So values of 9999.00 up to 9999.99 would still be valid.
-- Alan Bourke alanpbourke (at) fastmail (dot) fm
On Mon, 8 Aug 2016, at 11:18 AM, Paul Newton wrote:
Hi Laurie
I am playing around with that just now but we have hundreds of tables with thousands of numeric fields so it would be quite a task (but I could probably automate the process)
Thanks for the suggestion
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Laurie Alvey Sent: 08 August 2016 11:02 To: profoxtech@leafe.com Subject: Re: Width and Decimal (VFP) vs Precision and Scale (SQL)
Have you thought about creating a field validation rule say field1 <= 9.99?
Laurie
On 8 August 2016 at 10:47, Paul Newton Paul.Newton@pegasus.co.uk
wrote:
Hi all
It's probably best if I explain this by example. Let's say we have a field in VFP with Width 4 and Decimal 2. In a browse the maximum value that we can enter is 9.99 (as would be expected). BUT by means of a REPLACE we could end up with values of 99.9, 999 or even 9999 without getting numeric overflow.
Because of this, and because we want to write away the VFP data to SQL Server we have been setting the equivalent SQL Server field (or more strictly column) to a Precision of 6 and Scale 2. Similarly a VFP field of Width 8 and Decimal 3 would have an equivalent SQL Server column of Precision 11 and Scale 3.
This isn't really very satisfactory and I was wondering how other people deal with this. Ideally our code should not allow a replace with values (in the first example) greater than 9.99. Of course VFP does complain if we try to replace the value with 99999 (Numeric overflow. Data was lost)
We would have preferred it if VFP were to complain in ALL cases where the replacement value is greater than 9.99 but, alas, that is not the
case.
Any thoughts, comments or suggestions would be very welcome.
Paul Newton
[excessive quoting removed by server]
Hi Alan
That is in fact what we are doing now but I am investigating what we perhaps ought to do in the future - for example maybe using field validation rules in VFP to limit the maximum value for N(4,2) to 9.99
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Alan Bourke Sent: 08 August 2016 11:47 To: profoxtech@leafe.com Subject: Re: Width and Decimal (VFP) vs Precision and Scale (SQL)
In your example maybe you'd just have to use type Decimal in SQL Server with precision = 6 and scale = 2.
So values of 9999.00 up to 9999.99 would still be valid.
-- Alan Bourke alanpbourke (at) fastmail (dot) fm
On Mon, 8 Aug 2016, at 11:18 AM, Paul Newton wrote:
Hi Laurie
I am playing around with that just now but we have hundreds of tables with thousands of numeric fields so it would be quite a task (but I could probably automate the process)
Thanks for the suggestion
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Laurie Alvey Sent: 08 August 2016 11:02 To: profoxtech@leafe.com Subject: Re: Width and Decimal (VFP) vs Precision and Scale (SQL)
Have you thought about creating a field validation rule say field1 <= 9.99?
Laurie
On 8 August 2016 at 10:47, Paul Newton Paul.Newton@pegasus.co.uk wrote:
Hi all
It's probably best if I explain this by example. Let's say we have a field in VFP with Width 4 and Decimal 2. In a browse the maximum value that we can enter is 9.99 (as would be expected). BUT by means of a REPLACE we could end up with values of 99.9, 999 or even 9999 without getting numeric overflow.
Because of this, and because we want to write away the VFP data to SQL Server we have been setting the equivalent SQL Server field (or more strictly column) to a Precision of 6 and Scale 2. Similarly a VFP field of Width 8 and Decimal 3 would have an equivalent SQL Server column of Precision 11 and Scale 3.
This isn't really very satisfactory and I was wondering how other people deal with this. Ideally our code should not allow a replace with values (in the first example) greater than 9.99. Of course VFP does complain if we try to replace the value with 99999 (Numeric overflow. Data was lost)
We would have preferred it if VFP were to complain in ALL cases where the replacement value is greater than 9.99 but, alas, that is not the case.
Any thoughts, comments or suggestions would be very welcome.
Paul Newton
[excessive quoting removed by server]
-
Alan Bourke -
Laurie Alvey -
Paul Newton