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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy