Except query behaving wierdly.Plz Help!!urgent

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Except query behaving wierdly.Plz Help!!urgent

Author

Topic

kgundava
Yak Posting Veteran

66 Posts

Posted - 2011-08-19 : 09:45:07

Hi All,
I have created two identical tables i.e.
Table A(2500 rows ) in sql server using linked server(oracle)
Table B(2500 rows) in sql server using linked server(Attunity)
Both these tables are expected to have identical data.So in order to validate that , i am running an except query
i.e. select * RDATAS except select * from RDATAT
Now the SQL server says there 112 rows which are different.In order to debug i ran select * from RDATAT except select * from RDATAS and this too returned 112 rows.
Now i exported them both to two different .csv files and compared using beyond compare tool and there were no differences at all :(. I do not see why except query is behaving weirdly. Please help me in figuring as i am in a time crunch :(............

Thanks

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-08-19 : 10:10:25

compare the data types of both tables.

if they are supposed to be EXACTLY the same, you can add row numbers to your output, then select the rows that showed up in your EXCEPT and look at the difference.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

kgundava
Yak Posting Veteran

66 Posts

Posted - 2011-08-19 : 14:06:18

Hi Don,
I found that the column which is giving me issues is a Float data type.Please see below for my problem in detail
1) We have archived an oracle Database which has 27000 tables into a proprietary format files.We can run Ansi sql standard queries on these files using a middleware.
2)To meet the regulatory requirements, now we have to validate all the 27000 tables which we have archived.
3)To do this i took help of SQL server 2008.I.e. i created linked servers both to the oracle and archived files(using middleware).
4)Now i create a table(Source) in sql server by randomly selecting X no of rows from oracle.Also i created a table(TARGET) in sql server by selecting the same rows from archived files .
5) Once i have both of these tables ready i run a except between the source table and target table.
6)Now the issue i have in hand is for float datatype, i.e. for a particular float datatype column i see the except query is not working as intended(mentioned above).The column data type is exactly same in both the tables i.e. Float, Not null
. I do not understand how to proceed.Please help me out in fixing this issue
Thanks......

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-08-20 : 05:39:40

Cast to a precise data type on both sides (like numeric). Float is inprecise, this can happen with it (and is documented to happen)

--
Gail Shaw
SQL Server MVP

kgundava
Yak Posting Veteran

66 Posts

Posted - 2011-08-20 : 12:27:47

Thanks a ton for the answer. I am doing all this data validation using a SQL script.
I create the tables dynamically in SQL server based on the ddl of oracle and archive files..see below...
1) Select @TABLE_CREATIONSource = 'select '+@COLNAMS+' into RDATAS from TEST..CUN.'+@TABLE_NAME+' where 1=2'
exec (@TABLE_CREATIONSource)
Select @TABLE_CREATIONTarget = 'select '+@COLNAMS7+' into RDATAT from TEST..CUN..'+@TABLE_NAME+' T where 1=2'
exec (@TABLE_CREATIONTarget)
2) Now i load data into both the tables from there respective sources
3) Now i run the except query
select * from RDATAS Except select * from RDATAT
Please let me know how can i say to sql server that if table has float data type then convert it to the Numeric datatype(also how should i set the precision??). I would want to do this before step 2 using a Alter Table syntax...

Thanks..

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-08-20 : 15:21:49

You can do the conversion to exact numeric data type during the query itself. For example:


select cast(colA as decimal(19,5)) from RDATAS 
Except 
select cast(colB as decimal(19,5)) from RDATAT

How much precision you want to use (I have used 5) depends on how much you can tolerate. I would start with a higher value (for example, decimal (19,10)) and see if you see any differences. If you do see differences, you can see in the results of the query why and by how much they are different.

kgundava
Yak Posting Veteran

66 Posts

Posted - 2011-08-20 : 15:42:50

Thank Sunita...But i have to do this dynamically so i would like to do it using a Alter Table statement where i should be able to change all columns with the float data types to a numeric data type.As i said i am doing all this in a script for 25,000 + tables, i should be able to also find the right precision which should work for all the tables across..Please let me know your thoughts.....

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-08-20 : 16:10:59

How dynamic does it have to be? If you are doing it only for a few tables, I would just look up the columns and convert them to the appropriate data types as required manually. It is much safer and less error-prone (in my humble opinion, of course).

If you do want to alter the float data types to decimal, I suppose you can do it something like this. But if you can avoid using this, avoid it.

DECLARE @sql NVARCHAR(4000);
WHILE (1=1)
BEGIN
	SET @sql = NULL;
		
	SELECT TOP 1 @sql = 'alter table RDATAS alter column ' + column_name + ' decimal(18,5)' FROM 
	INFORMATION_SCHEMA.[COLUMNS] c WHERE c.TABLE_NAME = 'RDATAS' AND c.DATA_TYPE = 'float';
	
	IF (@sql IS NULL) BREAK;
	
	exec sp_executesql @sql;
END;

Run this (for RDATAS table) and a similar query for the other table just before you run the EXCEPT query.

kgundava
Yak Posting Veteran

66 Posts

Posted - 2011-08-20 : 16:30:43

Thanks a Ton for the reply...
Doing this manually is not a option as i have around 1500 tables and the tables itself are created at runtime. I am creating these tables on the DDL's of the Oracle and Archvive files(linked server).
The only concern i have is whether this precision would work for all the tables or not.Please let me know how can i handle this . I am really sorry for asking so much help but cant help as i am in a learning phase.
Thanks...

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-08-20 : 17:33:08

I can't say if this precision will work or not. For two reasons:

First, since the data comes from two different DBMSs, there may be differences introduced by the manner in which each stores and computes floating point data.

Second, even on the same DBMS, based on how the data was generated - for example, result of a floating point computation vs result of a decimal data type computation - two floating point values one would think would be the same may not be the same.

Determine from your business clients what level of precision is acceptable and go with that.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-08-20 : 17:41:37

The code below is an example of what I meant when I said in my previous post that the data one would think would be the same may not be the same depending on how it was generated, even on the same DBMS. The behavior is what I observed on my system. You may see a different behavior depending on your versions of windows and SQL.


-- create a test table.
create table #tmp(f float);

-- insert two rows into it, they should be exactly the same if the data was stored
-- as exact numeric.
insert into #tmp values 
	(3.14159265358979323846264338327950288*1.0e-2),
	(3.14159265358979323846264338327950288*0.01)

-- but they are not, because data type float is not an exact numeric type.
-- this query gives me two rows.
SELECT DISTINCT f FROM #tmp;

-- but if I cast it to decimal they appear to be the same.
-- this gave me only one row.
SELECT DISTINCT cast(f AS DECIMAL(19,5)) FROM #tmp;

TRUNCATE TABLE #tmp;

-- try again with different values
insert into #tmp values 
	(31415926535897.9323846264338327950288*1.0e-2),
	(31415926535897.9323846264338327950288*0.01)
	
-- in this case it didn't work like it did the last time.
-- this gave me two rows.
SELECT DISTINCT cast(f AS DECIMAL(19,5)) FROM #tmp;

DROP TABLE #tmp;

kgundava
Yak Posting Veteran

66 Posts

Posted - 2011-08-20 : 18:29:55

Thanks for your detailed explanation.Based on what you said there is no one precision that would guarantee to work on all tables.
So Please let me know if rounding function can be used to make the data same in both the tables....
Thanks...

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-08-21 : 08:09:20

You can round the float columns via select. You cannot set the property of a float column to be rounded to a specific number of decimal digits. So you will need to rewrite your EXCEPT query to list each column and round it to the appropriate number of decimal digits if it is FLOAT. But, doing so will preserve the type and precision of the float columns.

You will still have the two issues that I mentioned earlier to deal with:

1. What precision is acceptable to your clients?
2. You may still not get exact match as I had shown in my earlier example.

kgundava
Yak Posting Veteran

66 Posts

Posted - 2011-08-21 : 11:56:40

Hi Sunita,
Unfortunately it is FDA regulated Environment and we cannot compromise with the precision.I have to find out a way where i can still keep the precision intact and achieve the desired result.I am open to multiplying the values with a value such as 10000 ...Please let me know if you have any other ideas too.
Thanks......

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-08-21 : 15:01:05

I don't have many ideas, other than to suggest to check on the remote systems to see if they are using exact numerics (equivalent of DECIMAL, MONEY etc. on SQL Server) or approximate numerics (FLOAT, REAL).

a) If the remote systems are using exact numerics, create the tables on SQL Server with a data type that is slightly more precise than that and then import.

b) If they are using approximate numerics, there is nothing much you can do because the data is already approximate. However, you may still want to pre-create the tables in SQL Database with decimal data types with a high precision to avoid the possibility that SQL Server is causing the imprecision.

Also, you can your query as is, and pick one pair of data that is different between the two systems. Then, look up how that data is stored in the remote systems. On SQL Server you can cast to varbinary to see the binary data. (select cast(col1 as varbinary) from YourTable). If the binaries themselves differ (allowing for how the systems store the data), then you will need to tell the FDA that the data on the two disparate systems are different, so there is nothing you can do.

kgundava
Yak Posting Veteran

66 Posts

Posted - 2011-08-23 : 10:33:09

Thanks a lot for all the help ......U r awesome :):)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources