Cast as Decimal

[Scott Bailey]

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.

[Erland Sommarskog]

Post by Scott Bailey
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.

[scott]

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.

Post by Erland Sommarskog

Post by Scott Bailey
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.
--
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Erland Sommarskog]

Post by scott
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.

Post by scott
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

[scott]

thanks.

Post by Erland Sommarskog

Post by scott
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.

Post by scott
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
--
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx