CODEWITHSUNDEEP
sql-serveroracle-databaseoracle11g
DaeHee Kim
DaeHee Kim

Reputation: 5

SQL Server : Top 1 query to Oracle (11g) not 12c

Here is a single SQL statement in SQL Server

SELECT 
    a.EMPNUM,
    (SELECT TOP 1 NAMEKO 
     FROM PA0001 
     WHERE COMPCD = a.COMPCD 
       AND EMPNUM = a.LASTUP 
     ORDER BY EDDATE DESC) AS LASTNM,
    (SELECT TOP 1 NAMEKO 
     FROM PA0001 
     WHERE COMPCD = a.COMPCD 
       AND EMPNUM = a.FRSTUP 
     ORDER BY EDDATE DESC) AS FIRSNM
FROM PA0000 a

Then, I tried to apply it to Oracle 11g (not Oracle 12c which supports the top n query)

SELECT 
    empnum,  
    (SELECT NAMEKO 
     FROM 
          (SELECT NAMEKO 
           FROM PA0001 
           WHERE COMPCD = a.COMPCD 
             AND EMPNUM = a.FRSTUP 
           ORDER BY EDDATE DESC) 
     WHERE ROWNUM = 1) AS FRSTNM,  
    (SELECT NAMEKO 
     FROM 
          (SELECT NAMEKO 
           FROM PA0001 
           WHERE COMPCD = a.COMPCD 
             AND EMPNUM = a.LASTUP 
           ORDER BY EDDATE DESC) 
     WHERE ROWNUM = 1) AS LASTNM
FROM PA0000 a

Then I got an error

ORA-00904: "A"."FRSTUP": invalid identifier.

How can I rewrite the query for Oracle?

Upvotes: 0

Views: 882

Answers (3)

Smruti Ranjan Parija
Smruti Ranjan Parija

Reputation: 1

As per my understanding from your question you can achieve the result by using level, connect by prior as follows:

SELECT EMPNO FROM EMP WHERE HIREDATE IN
(SELECT MAX(HIREDATE) FROM EMP WHERE LEVEL<=1 CONNECT BY PRIOR HIREDATE>HIREDATE GROUP BY LEVEL) ORDER BY HIREDATE DESC;

Hope this will solve your requirement...

Upvotes: 0

Olafur Tryggvason
Olafur Tryggvason

Reputation: 4874

using rownum=1 will select a random value not dependent on the order by clause (rather how the blocks are aligned on disk)

12c has this feature ( FETCH FIRST {n} ROWS ONLY clause )

In 11.2 and less, using row_number () is the most convenient way

Example of selecting the the latest object (single) created in the schema:

select *
  from (select user_objects.object_name,
               user_objects.object_id,
               user_objects.created,
               row_number () over (order by user_objects.created desc) as rn
          from user_objects) view_uo
 where view_uo.rn = 1

Viewing the latest 10 objects would be rn <= 10

Then just modify this to include your selects.

Upvotes: 1

Roman Marusyk
Roman Marusyk

Reputation: 24589

First you need to add alias "a" to table PA0000 to avoid error ORA-00904: "A"."FRSTUP": invalid identifier.

Upvotes: 1

Related Questions