Re: Width and Decimal (VFP) vs Precision and Scale (SQL)

-- 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]