Problem with numeric data with SQLEXEC()

[Rogy]

I am having problem with retreiving numeric data from SQL.

In VFP8 I have:
SQLEXEC(anav,'SELECT

, [Unit price] FROM [ZCDatabase d_d_$Sales Price]','curs1')

The field 'unit price' is decimal type on SQL Database, but in VFP cursor I get N(20,20) type! 

Is there a way to change format of numeric type?

 

[Olaf Doschke]

decimal, with which width? Use ca cast to get a field type VFP can manage, eg Integer:

...CAST([Unit price] AS int) as Unit_Price...

Bye, Olaf.

 

[Rogy]

Hello
I've tried with this function, but still don't get the result I want.
I need decimal value, with decimal places. I tried several combinations with CAST and CONVERT, but insted getting the value 2878,00 i get 2,88 (!?)

The SQL data is decimal(38,20).

 

[Imaginecorp]

First the maximum number of decimals in VFP are 18. for your needs Your field size should be (38+20+1).

Try this:
SELECT TRANSFORM(amount,"###,###,###.##################") ;
FROM ALIAS()
or
SELECT amount, CAST(TRANSFORM(amount,"###,###,###.##################") as n(20,18)) as amt ;
FROM ALIAS()
This creates a temprory field called amt.
BUT: remember for the second select, the amount fields length MUST be 59

 

[Imaginecorp]

(20,18) should be (59,18), now you will have 38 integers and 18 decimal places

 

[Rogy]

TRANSFORM is VFP function not an SQL one. So: SELECT amount, CAST(TRANSFORM(amount,"###,###,###.##################") as n(20,18) doesn't work.
The (59,18) is also too long for VFP.

 

[Imaginecorp]

Yes; Sorry about transform... so do not use transform, try this

SELECT amount,CAST(amount as n(20,18)) as amt ;
FROM ALIAS()

 

[Olaf Doschke]

You have to do the CAST on SQL Server. Sorry that I didn't came back earlier. SQL Server defines N(x,y) different than VFP, VFP can have maximum 20 places in all, including the decimal point and sign. So you need to decide how many places you want before the decimal point and can maximum have 20-2-that number as decimal precision.

If it's the usual preciaion of 4 or 5 decimal places for currency data, you'd use CAST([Unit Price] AS N(13,5)) AS Unit_Price within the SQLEXEC or try N(12,5) if that does not work.

Bye, Olaf.

 

[Rogy]

It is like this:
I've tried your version, then I tried with:
SELECT

, [Unit price], CONVERT(numeric(20,2),CONVERT(decimal(38,20), [Unit price])) as Unit_Price1 FROM
I also tried with CAST(), then all with VFP 8.0 and 9.0, but the result is not OK.
It is obviously VFP issue, because SQL 2005 query is giving me the right result.
What I figured out is that numbers below 999,99 are shown the right way, but numbers above 1000,00 are not OK.
SQL Data      VFP Data
  826.00         826.00
 2850.00           2.85
 5148.00           5.15
The values are also rounded:
   55.63          55.00

Don't know what else to do.

 

[Olaf Doschke]

give it less decimal places, and you can have higher numbers.

I don't understand what is so complicated. Rereading the SQL Server help decimal(38,20) means a total of 38 digits with 20 deimal places, which is overly precise for prices and goes up to quintillions.

SQL server does interpret decimal(x,y) a little different than VFP interprets N(x,y). With this knowlegde it's just a little bit of testing.

decimal(38,20) besides a little different storagte than numeric(38,20) means 38 places, 20 of them decimal places after the decimal point. VFP can only support 20 places in total, so if you want to support prices up to millions and with 2 decimal places, then it's N(11,2) for VFP, because VFP counts the decimal point as a place and reserves one for the sign, and in SQL Server you'll need numeric(9,2) or decimal(9,2).

You can stay with decimal(38,20) in SQL, but use CAST([Unit Price] As decimal(9,2)) As Unit_Price works for example, resulting in N(11,2) on the vFP side. decimal(12,5) works too and results in N(14,5).

Now just remember VFP can go up to maximum N(20,d) with d maximum 18 and you knwo what you can CAST or convert to in SQL Server, that can be transferred to VFP.

lnH=SQLStringConnect("DRIVER={SQL Server};SERVER=...;Trusted_Connection=YES;DATABASE=...")
SQLExec(lnH,"Select CAST([Unit Price] As decimal(9,2)) As Unit_Price From Prices","curTest")
SQLExec(lnH,"Select CAST([Unit Price] As decimal(12,5)) As Unit_Price From Prices","curTest2")

Select curTest2
Afields(laFields)
? laFields(1,1), laFields(1,2), laFields(1,3), laFields(1,4)

If you still see less decimal places, check SET FIXED and SET DECIMALS, which both influence the *display* of numbers.

Bye, Olaf.