Cast as Decimal

myTable in the below code examples resides in a linked Visual FoxPro
database. myTable contains a field called myDecimalField as well as several
others exactly like it and are of Decimal (9,1) not null type.
All the other fields select fine in SQL, but myDecimalField gives the ERROR
below when I SELECT it.
Just for a test, I CASTed myDecimalField in CODE 1 below as a VarChar type
and SQL returns it fine. So, I tried CODE 2 below and tried to CONVERT the
VarChar CAST and I get the same ERROR below.
Can someone help me with syntax in CODE 2 and convert myDecimalField into a
DECIMAL format so I can retain the fields decimals and numberic type?
**********************
CODE 1 (works):
SELECT myIdField, CAST(myDecimalField AS
VARCHAR(55)) AS myDecimalField FROM myTable
CODE 2 ( doesn't work):
SELECT myIdField, CONVERT(DECIMAL(18, 4),
CAST(myDecimalField AS VARCHAR(55))) AS myDecimalField FROM myTable
ERROR:
OLE DB error trace [OLE/DB Provider 'VFPOLEDB'
IRowset::GetData returned 0x80040e21: Data status returned from the
provider: [COLUMN_NAME=myDecimalField
STATUS=DBSTATUS_E_UNAVAILABLE]].
Msg 7341, Level 16, State 2, Line 1
Could not get the current row value of column
'[VFPOLEDB].myDecimalField' from the OLE DB provider 'VFPOLEDB'. The
provider cannot determine the value for this column.Scott Bailey (sbailey@.mileslumber.com) writes:
> myTable in the below code examples resides in a linked Visual FoxPro
> database. myTable contains a field called myDecimalField as well as
> several others exactly like it and are of Decimal (9,1) not null type.
> All the other fields select fine in SQL, but myDecimalField gives the
> ERROR below when I SELECT it.
> Just for a test, I CASTed myDecimalField in CODE 1 below as a VarChar type
> and SQL returns it fine. So, I tried CODE 2 below and tried to CONVERT the
> VarChar CAST and I get the same ERROR below.
> Can someone help me with syntax in CODE 2 and convert myDecimalField
> into a DECIMAL format so I can retain the fields decimals and numberic
> type?
The situation certainly looks spooky, but the root problem is obviously
a problem with FoxPro, or the FoxPro provider. I would guess that there
some rows where myDecimalField has some illegal value.
Assuming that myTable has an column called id, of which the lowest value
is 1, and the highest is 100, you could do
SELECT ... FROM myTable WHERE id BETWEEN 1 AND 50
If that gives the error, narrow down the interval to 1 AND 25 and so on.
Of course it's a good idea to look at the data from FoxPro as well.
If you want an SQL Server solution, you would have to bounce the data
over a temp table, so the conversion from varchar takes place in
SQL Server.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Couple of things:
1. The error occurs on all records so i know it's not bad data, plus there's
another field with same problem.
2. Could you provide some syntax example of creating a temp table with the
varchar conversion and transferring it as you suggested? I've never used a
temp table before.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97A9BCADA85Yazorman@.127.0.0.1...
> Scott Bailey (sbailey@.mileslumber.com) writes:
> The situation certainly looks spooky, but the root problem is obviously
> a problem with FoxPro, or the FoxPro provider. I would guess that there
> some rows where myDecimalField has some illegal value.
> Assuming that myTable has an column called id, of which the lowest value
> is 1, and the highest is 100, you could do
> SELECT ... FROM myTable WHERE id BETWEEN 1 AND 50
> If that gives the error, narrow down the interval to 1 AND 25 and so on.
> Of course it's a good idea to look at the data from FoxPro as well.
> If you want an SQL Server solution, you would have to bounce the data
> over a temp table, so the conversion from varchar takes place in
> SQL Server.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||scott (sbailey@.mileslumber.com) writes:
> 1. The error occurs on all records so i know it's not bad data, plus
> there's another field with same problem.
Weird. But I'm not a Foxpro person, so I have no idea of what could
be going on.

> 2. Could you provide some syntax example of creating a temp table with the
> varchar conversion and transferring it as you suggested? I've never used a
> temp table before.
CREATE TABLE #spookydecimal (id int NOT NULL,
decvalue varchar(55) NULL)
-- Add other columns as needed.
INSERT #spookydecimal(id, decvalue)
SELECT myIdField, CAST(myDecimalField AS
VARCHAR(55)) AS myDecimalField
FROM myTable
SELECT id, decvalue, case(decvalue as decimal(18,4))
FROM #spoookydecimal
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||thanks.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97A9DB71F9EE0Yazorman@.127.0.0.1...
> scott (sbailey@.mileslumber.com) writes:
> Weird. But I'm not a Foxpro person, so I have no idea of what could
> be going on.
>
> CREATE TABLE #spookydecimal (id int NOT NULL,
> decvalue varchar(55) NULL)
> -- Add other columns as needed.
> INSERT #spookydecimal(id, decvalue)
> SELECT myIdField, CAST(myDecimalField AS
> VARCHAR(55)) AS myDecimalField
> FROM myTable
> SELECT id, decvalue, case(decvalue as decimal(18,4))
> FROM #spoookydecimal
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx