CODEWITHSUNDEEP
sql-serveroracle-databaseoracle11gsql-server-2012
Al.
Al.

Reputation: 875

Oracle equivalent of a SQL Server query

I need to know the Oracle equivalent of my SQL Sever query. Could someone please help me?

select recno = (select top 1 ld.recno from load ld where ld.crecno = i.recno) 
from inputtable i

Upvotes: 0

Views: 318

Answers (2)

Richard Pascual
Richard Pascual

Reputation: 2021

Converting TOP(N) T-SQL Syntax to Oracle Compatible SQL

The SUBQUERY you wrote is not necessary. A INNER JOIN operation between the LOAD and INPUTTABLE tables is a quick way to get the set you are looking for.

SQL Syntax Interpretation: Of all the records in LOAD, how many of the ones with values CRECNO have matching values in the column RECNO of table INPUTTABLE? Taking those results, what is the FIRST value (TOP(1)) in the output if the results are sorted by...(?)...

There isn't any SORT priority designated in the OP.

In a SELECT statement, always use an ORDER BY clause with the TOP clause. This is the only way to predictably indicate which rows are affected by TOP. Reference From: Microsoft Technet.

To illustrate a correctly formatted SQL statement with repeatable results, I went ahead and rewrote the OP's SQL query assuming that the TOP(n) solution wanted the first value in an ASCENDING SORT of the RECNO column values.

Database servers do have a default sorting and selection behavior if important expressions such as an ORDER BY statement are missing, it is risky however to assume that all defaults are set the same in any given environment.

The Rewritten SQL:

WITH sub_query AS (
     SELECT i.recno
       FROM inputtable i, load ld
      WHERE i.recno = ld.crecno
      ORDER BY i.recno ASC
     )
     SELECT s.recno
       FROM sub_query s
      WHERE rownum = 1

The ROWNUM evaluation and the ORDER BY criteria can be changed to obtain the TOP(n) behavior intended.

Upvotes: 1

Pragnesh Khalas
Pragnesh Khalas

Reputation: 2898

Check Below Query

select (select  ld.recno from load ld where ld.crecno = i.recno AND RowNum =1)
AS recno  from inputtable i

Upvotes: 1

Related Questions