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:
Post a Comment