Sql Script with % operator not working in Sql Server and Oracle, same way

By Vinod Bapatla

I got stuck with a query that has to be standardized to work for Sql Server and Oracle, but has an operator that is only defined in SqlServer. The first thing that I got to my mind is to write a wrapper with same signature in both environments. How...

The query that I was working was little complex. I am just providing a portion of it for explanation.
UPDATE ScheduledShifts
SET Duration = 1440 + (((ScheduledOutTime / 100) * 60) + (ScheduledOutTime % 100)) -
                             (((ScheduledInTime / 100) * 60) + (ScheduledInTime % 100)) - 3  

The equivalent in Oracle is:
UPDATE ScheduledShifts
SET Duration = 1440 + (((ScheduledOutTime / 100) * 60) + MOD(ScheduledOutTime,100)) -
                              (((ScheduledInTime / 100) * 60) + MOD(ScheduledInTime,100)) - 3

But I wanted same SQL statement to work in both SqlServer and Oracle and not two specific statements, one for each.
So, I have to wrap the functionality in a function that has same signature for both SqlServer and Oracle.
By god's grace, Oracle helped me by providing a function for the operation (MOD) and I just need to use the signature of MOD function of Oracle and write a wrapper for Sql Server. It goes something like this:
CREATE FUNCTION [dbo].[MOD](@numerator int, @denominator int)
   RETURNS int
   AS
   BEGIN
         DECLARE @ret int;
        IF @denominator is NULL
               BEGIN
                     SET @ret = NULL
              END        
        ELSE
               BEGIN
                    @ret = @numerator % @denominator
               END
         IF (@ret IS NULL)
                 SET @ret = NULL
        RETURN @ret
   END

Sql Script with % operator not working in Sql Server and Oracle, same way  (715 Views)