Oracle Query to SQL Server Query

Need support to convert SQL Server query to Oracle

You can use ROWNUM:

Select 
     Data-A,
     Data-B,
     (Select * from (
        (
            Select data-C from TableC 
            Where TableC.SourcePtr=Tabbleb.Rkey order by Step Desc
        )
      where rownum = 1 ) as Data-C
From TableA,
     TableB
Where TableA.Source_PTR =TableB.Rkey 

or you can use FETCH NEXT N ROWS ONLY:

Select 
     Data-A,
     Data-B,
     (Select DATA-C from TableC 
      Where TableC.SourcePtr=Tabbleb.Rkey order by Step Desc
      FETCH NEXT 1 ROWS ONLY) as Data-C
From TableA,
     TableB
Where TableA.Source_PTR =TableB.Rkey 

Oracle query to SQL Server query

this is a standard sql query syntax!

SQL statement joining Oracle and MS SQL Server

Yes- both Oracle and SQL Server support the linked server concept. That allows you to reference the other server using a 4 part name. For example:

select  *
from    LocalDb.Schema.Table
cross join
        OracleLinkedServer.RemoteDb.RemoteSchema.RemoteTable

How can I convert a small query from SQL Server to Oracle, I don't know how to apply their equivalents in reserved words?

You want something like (although second_bdd.owner.clients looks wrong as it should be schema_name.table_name and not schema_name.table_name.something_else):

CREATE PACKAGE PKG_HOME AS
PROCEDURE SP_HOME_DATA (
  i_from   IN  timestamp,
  i_to     IN  timestamp,
  i_start  IN  number,                
  i_page   IN  number,
  o_cursor OUT SYS_REFCURSOR
);
END PKG_HOME;
/

CREATE PACKAGE BODY PKG_HOME AS
PROCEDURE SP_HOME_DATA (
  i_from   IN  timestamp,
  i_to     IN  timestamp,
  i_start  IN  number,                
  i_page   IN  number,
  o_cursor OUT SYS_REFCURSOR
)                          
AS
BEGIN
  OPEN o_cursor FOR
  with cte as (
    SELECT h.id_city
    FROM   main_bdd.home h  
           left join second_bdd.owner.clients cl
           on cl.idHome = h.idHome                  
    WHERE  h.day + INTERVAL '1' SECOND * h.datehome
             BETWEEN i_from AND i_to
    ORDER BY h.datehome asc                 
    OFFSET i_start ROWS FETCH NEXT i_page ROWS ONLY
  )
  SELECT h.id_city,
         h.last_ubication
  FROM   main_bdd.home h  
         LEFT JOIN second_bdd.owner.clients cl
         on cl.idHome = h.idHome 
  WHERE  h.day + INTERVAL '1' SECOND * h.datehome
             BETWEEN i_from AND i_to
  AND    EXISTS(
           select 1 from cte where cte.id_city = h.id_city
         )
  order by h.datehome asc;
END; 
END PKG_HOME;
/

Note: In Oracle '' and NULL are identical so using COALESCE(value, '') (the Oracle equivalent of the ISNULL function) is identical to value without any wrapped function.

db<>fiddle here

---

Related Topics