Converting SQL Server query to Oracle - specific cases here

Question

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?

Yahia · Accepted Answer · 2012-01-23 21:41:40Z

2

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.

edited Jan 23, 2012 at 21:41

answered Jan 23, 2012 at 21:09

Yahia

70.5k9 gold badges117 silver badges147 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Pinchas K Over a year ago

thanks for help Yahia. So that I can leave the datediff/add or I should change them?

Yahia Over a year ago

@PinchasK use + and - instead.

user330315 Over a year ago

The expression L_SPLIT_DATE = ... is not valid in Oracle. It must be done using a standard column alias NVL(...) AS L_SPLIT_DATE

davidmontoyago · Accepted Answer · 2012-01-23 22:31:31Z

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.

Collectives™ on Stack Overflow

Converting SQL Server query to Oracle - specific cases here

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related