Total Pageviews

2015/12/07

[Oracle to SQL Server Migration] MINUS

Problem
Here has a SQL statement which is working fine in Oracle:
1
2
3
4
5
6
7
8
9
select distinct fund_id
FROM dbm034fa

MINUS

select distinct fund_id
FROM dbm034fa
WHERE ( (mge_type='G' and (in_amt/out_amt)*100 >= 50) or
         (mge_type='F' and (in_amt/out_amt)*100 >= 20) )

But MINUS does not support in Microsoft SQL Server, how to translate it ? 


How-To
You can utilize EXCEPT in Microsoft SQL Server instead.
1
2
3
4
5
6
7
8
9
select distinct fund_id
FROM dbm034fa

EXCEPT

select distinct fund_id
FROM dbm034fa
WHERE ( (mge_type='G' and (in_amt/out_amt)*100 >= 50) or
         (mge_type='F' and (in_amt/out_amt)*100 >= 20) )


Reference
[1] http://blog.sqlauthority.com/2008/08/07/sql-server-except-clause-in-sql-server-is-similar-to-minus-clause-in-oracle/

No comments: