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 (162 Views)