How to Convert SQL Server to Oracle

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 

Convert() SQL Server to Oracle

• Converting any strongly-typed date, datetime, datetimeoffset, etc column to char/varchar is a code-smell and a strong hint that you're doing something wrong because you should never need to format-dates to perform date/time processing in SQL.
• Oracle doesn't have a datetime type like SQL Server does, instead it has a timestamp type (which has absolutely nothing to do with SQL Server's own timestamp type which is an alias for rowversion but I digress).
• Oracle's timestamp type is parameterised to specify the amount of precision for sub-second resolution - so simply CASTing your value to timestamp(0) will effectively zero-out the milliseconds component.
• You can also use the EXTRACT function to obtain the integer value (0-999) of the milliseconds component of a timestamp value.

UPDATE
    table
SET
    ValidFrom = CAST( ValidFrom AS timestamp(0) )
WHERE
    ValidFrom IS NOT NULL

---

Here's a working demo in SQLFiddle, using Oracle 11g R2:

Build Schema Script:

CREATE TABLE testTable (
    ValidFrom timestamp(7) NOT NULL
);

INSERT INTO testTable ( ValidFrom )
WITH v AS ( 
    SELECT TIMESTAMP '1997-01-08 11:11:11.111' AS ts FROM dual UNION ALL 
    SELECT TIMESTAMP '1997-02-09 12:22:22.222' AS ts FROM dual UNION ALL 
    SELECT TIMESTAMP '1997-03-10 13:33:33.333' AS ts FROM dual UNION ALL 
    SELECT TIMESTAMP '1997-04-11 14:44:44.444' AS ts FROM dual
) 
SELECT ts FROM v

Query script:

SELECT
  ValidFrom,
  CAST( ValidFrom AS timestamp(0) ) AS ValidFrom_withoutMilliseconds,
  ( ValidFrom - CAST( ValidFrom AS timestamp(0) ) ) AS diff
FROM
  testTable

//

UPDATE
    testTable
SET
    ValidFrom = CAST( ValidFrom AS timestamp(0) )
WHERE
    ValidFrom IS NOT NULL

//

SELECT
  ValidFrom,
  CAST( ValidFrom AS timestamp(0) ) AS ValidFrom_withoutMilliseconds,
  ( ValidFrom - CAST( ValidFrom AS timestamp(0) ) ) AS diff
FROM
  testTable

• Today I learned that // is the query separator, which is separate from the "query terminator" in Oracle.
• To my surprise, Oracle Database still doesn't support milliseconds with EXTRACT, fortunately that isn't necessary for this query.

Convert SQL Server / T-SQL query to Oracle PL/SQL

Important difference between MS Sql and Oracle is that in MS

SELECT @inputData = column_name FROM table;

runs OK even with no predicates i.e. with multirows result set, returning the value of column_name from an arbitrary row. In Oracle

SELECT column_name INTO input_data FROM my_table;

will fail if the result set contains more then one row.
You shouldn't just translate syntax thoughtlessly.

How to convert sql query with in clauses into string

One option is to use the q-quoting mechanism (see line #6), as it lets you use single quotes normally (also, your single quotes look fancy; if you planned to copy/paste from e.g. MS Word into your SQL client, that won't work).

Here's an example:

SQL> declare
  2    l_str varchar2(500);
  3    strcond varchar2(100) := '  and 1 = 1';
  4  begin
  5    l_str := 'select col1, col2 from table1 where col3 in ' ||
  6             q'[('cat', 'dog')]' || strcond;
  7    dbms_output.put_line(l_str);
  8  end;
  9  /
select col1, col2 from table1 where col3 in ('cat', 'dog')  and 1 = 1

PL/SQL procedure successfully completed.

SQL>

---

Related Topics