CODEWITHSUNDEEP
sqloracle-database
Pinchas K
Pinchas K

Reputation: 1571

Converting SQL Server query to Oracle - specific cases here

I am converting a 300 line sql server query to Oracle, some statements have me stuck, any help here would be fantastic:

1. SELECT L_SPLIT_DATE = NULLIF(SPLIT_DATE, CONVERT(DATETIME, '20000101', 112)) + 1
    FROM OGEN.GEN_P_ARCHIVE

 ----- Not sure about NULLIF and CONVERT. Datetime i think has to become DATE.

2. IF @@ROWCOUNT = 0  

   ---- 2 "@' signs, do I just remove them both?

3. SET @L_EDT = DATEDIFF(dd, 0, DATEADD(dd, 0, @P_EDT)) + (@L_EDT - DATEDIFF(dd, 0, DATEADD(dd, 0, @L_EDT))) + CASE WHEN @L_SDT > @L_EDT THEN 1 ELSE 0 END;

DATEDIFF, DATEADD, are these the same in Oracle?

Upvotes: 1

Views: 474

Answers (2)

davidmontoyago
davidmontoyago

Reputation: 1833

Is that a query? I don't know much about SQL Server, but what you might need there is a PL/SQL block...

anyways for a query the SELECT should be like this, note the alias L_SPLIT_DATE (not an assignation):

SELECT (NVL(SPLIT_DATE, TO_DATE('20000101', 'YYYYMMDD')) + 1) L_SPLIT_DATE FROM OGEN.GEN_P_ARCHIVE

for the ROWCOUNT you have to execute the count as an independent query unless the last statement is an update or delete DML, but sorry, not for the select.

the thing here is the IF you have there, as a procedural instruction you have to know how to handle it with oracle, if you can't do it in PL/SQL, you will have to figure it out with an EXISTS validation or a subquery, it would be helpful if you show us what is after the IF.

for the DATEDIFF, DATEADD I quote @Yahia.

As for DATEDIFF and DATEADD - in Oracle you can just work with - and +, Oracle interpretes the operands as DATE and "number of days" (can be floating point) respectively.

Upvotes: 0

Yahia
Yahia

Reputation: 70369

As for the SELECT

SELECT NVL(SPLIT_DATE, TO_DATE ('20000101', 'YYYYMMDD')) + 1 AS L_SPLIT_DATE FROM OGEN.GEN_P_ARCHIVE

As for ROWCOUNT that is a bit more complex - without context that is hard to answer.

As for DATEDIFF and DATEADD - in Oracle you can just work with - and +, Oracle interpretes the operands as DATE and "number of days" (can be floating point) respectively.

Upvotes: 2

Related Questions