How to validate minus query in SQL server

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

How to validate minus query in SQL server

Author

Topic

shanmp
Starting Member

13 Posts

Posted - 2008-08-11 : 03:08:33

Hi all,

Please let me know the 'minus' function in SQL server, its working in Oracle but not in SQL server, I searched it and finally i got the result that i need to use 'NOT EXISTS' but its not giving me the exact result which i need.
Let me walk through an example:
There are two tables Employee & Emp. The column emp_id and emp_name is loaded from Emp to Employee.
Here is the query which i have written:
Select emp_id, emp_name from Employee
where not exists (select emp_id, emp_name from emp where emp_id=Employee.emp_id)

Though there are some discrepancies in the Employee table , the query results are not showing them exactly.
The transformation is populating three records in Employee table and some where null too
Data:
Emp table
Emp_Id Emp_Name
1 ABCD
Employee Table
Emp_Id Emp_Name
1 Null
1 ABCD
1 ABCD

Please let me know how to pick the records from Employee table such as
Emp_Id Emp_Name
1 Null
1 ABCD

Its not working in the NOT Exists query. Please let me know is there any other possibility to work out this. since there are million of records its impos to check it manually.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-11 : 03:16:04

SELECT DISTINCT Emp_ID, Emp_Name from Employee

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-11 : 03:17:25

MINUS does exist in SQL Server with version 2005 and later.
Read about EXCEPT in Books Online.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-11 : 03:19:57

[code]DECLARE @Emp TABLE (Emp_ID INT, Emp_Name VARCHAR(20))

INSERT @Emp
SELECT 1, 'ABCD'

DECLARE @Employee TABLE (Emp_ID INT, Emp_Name VARCHAR(20))

INSERT @Employee
SELECT 1, NULL UNION ALL
SELECT 2, 'ABCD'

SELECT *
FROM @Employee
EXCEPT
SELECT *
FROM @Emp[/code]

E 12°55'05.25"
N 56°04'39.16"

shanmp
Starting Member

13 Posts

Posted - 2008-08-11 : 06:27:29

Hi,
I am using TOAD for SQL server 3.1 , I am unable to use the function 'Except' . May i know any other possibilities to retrieve the unmatched records.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-11 : 06:36:45

[code]SELECT e.Emp_Id,e.Emp_Name
FROM Employee e
LEFT JOIN EmpTable e1
ON e1.Emp_Id=e.Emp_Id
WHERE e1.Emp_Id IS NULL[/code]

shanmp
Starting Member

13 Posts

Posted - 2008-08-11 : 06:55:07

I have given an example as both the tables have the same column name , but it will not work when the column name or different in Left Join. Please let me know is there any other possibility to retrieve the unmatched records.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-11 : 08:03:06

Visakh, what happened to the NULL record?

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-11 : 08:16:47

[code]declare @Emp table
(Emp_Id int,Emp_Name varchar(5))
insert into @Emp
select 1, 'ABCD' union all
select 3,'AAP'
declare @Employee Table
(Emp_Id int,Emp_Name varchar(5))
insert into @Employee
select 1, Null union all
select 1, 'ABCD' union all
select 2, 'ABCD' union all
select 2,'YRU' union all
select 3,'AAB' union all
select 3,'AAP'

select e1.* from
@EMployee e1
LEFT JOIN @Emp e
on e.Emp_Id=e1.Emp_Id
AND e.Emp_Name=e1.Emp_Name
WHERe e.Emp_ID IS NULL

output
----------------
Emp_Id Emp_Name
----------- --------
1 NULL
2 ABCD
2 YRU
3 AAB[/code]

shanmp
Starting Member

13 Posts

Posted - 2008-08-12 : 02:57:45

Hope I havent explain clearly what is my scenario. Let me explain it again. Its kinda testing whether the records are loaded properly from one table to another table. One is Emp and another one is Employee table. Now my job is to validate the records loaded into the Employee table. I have only one single record for the emp_id 1, but when the transformation is done its coming as three records instead of single records. So it means there are some issue in the transformation logic. Leaving it apart, now i need pick only the unmatched records from the Employee table.
I need only SQL query , left join will not work for all the combinations. I have given the example that both the table of same column name and same set , but if there are more columns coming from two different table , what would be the case. So kindly gimme the answer which should match all perspectives. Hope I am clear by writing this query.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-12 : 03:00:34

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-12 : 04:22:23

quote:
Originally posted by shanmp

Hope I havent explain clearly what is my scenario. Let me explain it again. Its kinda testing whether the records are loaded properly from one table to another table. One is Emp and another one is Employee table. Now my job is to validate the records loaded into the Employee table. I have only one single record for the emp_id 1, but when the transformation is done its coming as three records instead of single records. So it means there are some issue in the transformation logic. Leaving it apart, now i need pick only the unmatched records from the Employee table.
I need only SQL query , left join will not work for all the combinations. I have given the example that both the table of same column name and same set , but if there are more columns coming from two different table , what would be the case. So kindly gimme the answer which should match all perspectives. Hope I am clear by writing this query.

Not clear still. can you explain what you mean by this
I have given the example that both the table of same column name and same set , but if there are more columns coming from two different table , what would be the case
also provide some data examples to explain your scenario along with verbal description.

shanmp
Starting Member

13 Posts

Posted - 2008-08-14 : 01:50:20

In the Example which i have given , it has same column name so left join is possible but whereas I need to choose two column from two different tables the join will not work. Let me again give a clear example:
Here is a scenario:
Source tables: Emp , Location
Emp table columns: (Id, Name)
1 George
Location table columns: (Id, Location)
ID Location
1 LA
Target Tables: Emp_details
Emp_details columns: (Id, Name, Location)
Now the data are populated from Source table to target table:
The Target table should have one single row:
1 George LA.
But its populated wrongly as below:
1 George LA
1 null null
Now when i write query in Oracle SQL it works fine
Select id, name, location from Emp_details
minus
select e.id, e.name , l.location
from emp e, location l
This query will return the exact mismatch records as
1 null null
Whereas in SQL server , how to write the same query ? It has to be in simple ways rather than declare. For your information i used not exists function but its not showing the mismatch records.
Its just says Records 0 of 0. But i need to pick this particular record
1 null null.
How is that pos ?
Note: This is not the case for one record there are million records and the query should work in an efficient way. I just want to filter the mismatch records. Hope i m clear. Revert me in case of any other questions

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources