September 19, 2011 at 1:36 pm
I have a table A with following clmns, ( ID (int), Date (datetime), Value (float))
I have a view B with following clmns ( ID (int), Date (datetime), Value (float))
View B is a 'SELECT * FROM table C' in Access 2000 database. This is establish using a Linked Server Connection
In Table C (Access), I have the following columns (ID (NUMBER), DATE (date/time) , Value (Number)(Filed size : Double))
Im trying to locate the combination of ID's, Date & Return that is there in Table A and not in View B , by using the following query
SELECT * FROM A a WHERE NOT EXISTS
(SELECT b.ID,b.[DATE],b.[Value] FROM LinkedServerName...B b
WHERE a.ID=b.ID AND
a.[AS_OF_DATE]=b.[Date]
AND a.[value]=b.[value])
ORDER BY a.ID
There are times it also returns the matching data.
How can I match the Value column correctlty ?
September 19, 2011 at 1:48 pm
Most likely it's due to the usage of FLOAT and DOUBLE. There might be some rounding differences.
Do you really need FLOAT on the SQL Server side? I 'd recommend to look into DECIMAL. And (just to be on the safe side) use CAST(b.[value] AS DECIMAL(p,s)) in the WHERE clause.
September 19, 2011 at 1:50 pm
Yes I need the column to be of type float as its being used by other process as well.
September 19, 2011 at 1:56 pm
What can be the maximum precision and scale for decimal, as the data coming from the Table C in the access database has no specific precision and scale.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply