How do I use ROWNUM in a subquery (emulating SQL Server TOP n)

From: Gosta Hulden <gosta_at_midworldproduction.com>
Date: 26 Nov 2004 02:04:03 -0800
Message-ID: <7dc8989e.0411260204.4ff3af90@posting.google.com>

As most of you might now, the common way of emulating SQL Servers TOP n is to use ROWNUM
SELECT TOP 1 fld1 FROM tbl ORDERBY fld1; becomes
SELECT * FROM (SELECT fld1 FROM tbl ORDER BY fld1) WHERE ROWNUM <= 1;

BUT, how can I do it in a sub query? I am migrating from SQL Server 2000 to Oracle 9.2, using ORACLE Migration Workbench version 10. The Original SQL Server query was:

---

SELECT DISTINCT
e.surname, e.firstname
FROM Employee e, EmployeeBenefitRecord ebr, EmployeePensionOption epo, PensionOption po, PensionScheme ps, BenefitProgramme bp, BenefitGroup bg

WHERE e.id = ebr.employeeId
AND ebr.id = epo.employeeBenefitRecordId
AND ebr.benefitGroupId = bg.id

AND bp.id = bg.programmeId
AND epo.pensionOptionId = po.id
AND po.pensionSchemeId = ps.id
AND e.companyId = @companyId
AND ( ebr.id = ( SELECT TOP 1 ebr2.id

                 FROM EmployeeBenefitRecord ebr2
                 WHERE ebr2.employeeId = e.id AND ebr2.status IN ('P',
'L', 'I')
                 ORDER BY ebr2.startDate DESC ) )

And the Workbench migrated that to:

---

SELECT DISTINCT e.surname, e.firstname
FROM Employee e, EmployeeBenefitRecord ebr, EmployeePensionOption epo, PensionOption po, PensionScheme ps, BenefitProgramme bp, BenefitGroup bg

WHERE e.id = ebr.employeeId 
AND ebr.id = epo.employeeBenefitRecordId 
AND ebr.benefitGroupId = bg.id 

AND bp.id = bg.programmeId
AND epo.pensionOptionId = po.id
AND po.pensionSchemeId = ps.id
AND e.companyId = XPENSIONBANDCROSSING.companyId_ AND (ebr.id = (

     SELECT * FROM ( 
         SELECT ebr2.id 
         FROM EmployeeBenefitRecord ebr2 
         WHERE ebr2.employeeId = e.id 
         AND ebr2.status IN ('P', 'L', 'I') 
         ORDER BY ebr2.startDate DESC ) 
     WHERE ROWNUM <= 1 ));

But this is not working since it is not possible to refer to e.id in the nested select statement. Does someone have a general workaround for this?

Thanks a lot
/Gosta Received on Fri Nov 26 2004 - 04:04:03 CST