Total Pageviews

2015/11/16

[Oracle to SQL Server Migration] ROWNUM

Problem
We used rownum = 1 in Oracle as bellows:

If we migrate to Microsoft SQL Server, how to translate it?
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
SELECT to_char(STA_DATE, 'YYYY') - 1911 ACCOUNT_YR,
       age,
       fund_id,
       (select fund_name from dbm031fa where fund_id=dbm034fa.fund_id and rownum=1) fund_name,
       mge_type,
       IN_AMT,
       OUT_AMT,
       (IN_AMT/OUT_AMT)*100 RATIO
FROM dbm034fa
WHERE to_char(STA_DATE, 'YYYY') - 1911 BETWEEN '101' AND '103'
      AND age = '379000000A'
      AND fund_id='010401C00010'
      AND mge_type='G'

How-To
We can use TOP N to fulfill this requirement.
The SQL statement will be amended as following:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
SELECT YEAR(STA_DATE) - 1911 ACCOUNT_YR,
       age,
       fund_id,
       (select TOP 1 fund_name from dbm031fa where fund_id=dbm034fa.fund_id) fund_name,
       mge_type,
       IN_AMT,
       OUT_AMT,
       (IN_AMT/OUT_AMT)*100 RATIO
FROM dbm034fa
WHERE YEAR(STA_DATE) - 1911 BETWEEN '101' AND '103'
      AND age = '379000000A'
      AND fund_id='010401C00010'
      AND mge_type='G'


Reference
[1] http://www.w3schools.com/sql/sql_top.asp
[2] https://msdn.microsoft.com/zh-tw/library/ms189463(v=sql.120).aspx

No comments: