After an Oracle migration, write out a function into a View SELECT statement

Question

After an Oracle migration, write out a function into a View SELECT statement

stevec883

Mr or Mrs. 500

Points: 500
More actions
February 25, 2025 at 2:15 pm
Go to Answer
#4545743
This is the function oracle.xxxfloat
BEGIN
IF @first IS NULL OR @second IS NULL
RETURN NULL
IF @first < @second
RETURN @first
RETURN @second
END
Here is the select statement getting column2, where I need to write the function 'oracle.xxxfloat' inline as part of the select
so I can phaze out the above function. In other cases it was straight forward, but not sure
how to approach this one. I'm pretty sure the @first would represent the first CASE, and @second would represent the second CASE, but not sure how to write them into the format of the function. Sorry if my question is not clear; I'm not very clear on how to write in the function. (Please ignore the other oracle function 'oracle.round' - I'm not removing that one yet).
I redacted too much info, apologies - this is correct (with column name changes/redacted)
SELECT
Column1,
oracle.xxxfloat(ISNULL(ssma_oracle.round_numeric_0(SUM(
CASE
WHEN Column8 LIKE '%E' THEN Column9
END) / NULLIF(SUM(
CASE
Column8 LIKE '%E' THEN Column10
END), 0) * 100), 0), 100) AS Column2,
- This topic was modified 1 month ago by stevec883.
- This topic was modified 1 month ago by stevec883.

Jonathan AC Roberts

SSCoach

Points: 18400
More actions
February 25, 2025 at 7:52 pm
Answer
#4545941
stevec883 wrote:
I'm trying to eliminate the function by rewriting the code where it is used. The listed function, Oracle.xxxfloat, I would like to remove from the column2 statement; (These functions were created by an Oracle migration tool, to convert the database to SQL). The function works just fine and produces a result set, this is just a part of one of the select statements (if I can figure this one out, it almost the exact same as the rest of the columns). My company wants all of the functions to just be written into the code (all Oracle tool created functions).
SELECT
Column1,
oracle.xxxfloat(ISNULL(ssma_oracle.round_numeric_0(SUM(
CASE
WHEN Column8 LIKE '%E' THEN Column9
END) / NULLIF(SUM(
CASE
Column8 LIKE '%E' THEN Column10
END), 0) * 100), 0), 100) AS Column2,
```
SELECT 
    Column1,
    CASE 
        WHEN ISNULL(ROUND(
            SUM(CASE WHEN Column8 LIKE '%E' THEN Column9 END) 
            / NULLIF(SUM(CASE WHEN Column8 LIKE '%E' THEN Column10 END), 0) * 100, 0
        ), 0) < 100 
        THEN ISNULL(ROUND(
            SUM(CASE WHEN Column8 LIKE '%E' THEN Column9 END) 
            / NULLIF(SUM(CASE WHEN Column8 LIKE '%E' THEN Column10 END), 0) * 100, 0
        ), 0) 
        ELSE 100 
    END AS Column2
```
February 28, 2025 at 5:29 pm

This was removed by the editor as SPAM
March 3, 2025 at 9:44 pm

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247109 More actions · Answer 1

DECLARE @first INT = 13
       ,@second INT = 2;

SELECT Result = CASE
                    WHEN @first IS NULL
                         OR @second IS NULL THEN
                        NULL
                    WHEN @first < @second THEN
                        @first
                    ELSE
                        @second
                END;

stevec883 Mr or Mrs. 500 Points: 500 More actions · Answer 2

Sorry Phil, I had to correct my statement. When I redacted our names, I redacted too much. I guess you could say I over redacted.

Phil Parkin SSC Guru Points: 247109 More actions · Answer 3

stevec883 wrote:

Sorry Phil, I had to correct my statement. When I redacted our names, I redacted too much. I guess you could say I over redacted.

OK (and good pun!). I don't do Oracle (and the code looks weird from a SQL Server point of view), so will leave this to someone who does.

stevec883 Mr or Mrs. 500 Points: 500 More actions · Answer 4

Thanks LOL - couldn't resist the pun. The code does not have any remarks to clarify. I can't understand what it is doing either -- just trying to get into the function format. Also, this is after a migration from Oracle to SQL, so the code is all T-SQL, no Oracle, but yeah...it's confusing. Thanks for taking a look.

Phil Parkin SSC Guru Points: 247109 More actions · Answer 5

Are you trying to rewrite the Oracle functions? In which case, you'll need their definitions, not just the code which is using them. If not, I don't really understand what you are trying to achieve.

stevec883 Mr or Mrs. 500 Points: 500 More actions · Answer 6

I'm trying to eliminate the function by rewriting the code where it is used. The listed function, Oracle.xxxfloat, I would like to remove from the column2 statement; (These functions were created by an Oracle migration tool, to convert the database to SQL). The function works just fine and produces a result set, this is just a part of one of the select statements (if I can figure this one out, it almost the exact same as the rest of the columns). My company wants all of the functions to just be written into the code (all Oracle tool created functions).

SELECT

Column1,

oracle.xxxfloat(ISNULL(ssma_oracle.round_numeric_0(SUM(

CASE

WHEN Column8 LIKE '%E' THEN Column9

END) / NULLIF(SUM(

CASE

Column8 LIKE '%E' THEN Column10

END), 0) * 100), 0), 100) AS Column2,

Phil Parkin SSC Guru Points: 247109 More actions · Answer 7

Understood. But in your database, the function definition will exist. Otherwise the code you provide would not execute.

You need to provide that (in the form of CREATE FUNCTION oracle.xxxfloat AS …) before anyone here can begin to write out the T-SQL code for you which will do it in-line.

stevec883 Mr or Mrs. 500 Points: 500 More actions · Answer 8

I didn't include the full "CREATE" statement, sorry to throw you off. I just listed what it contains at the beginning of my post:

This is the function oracle.xxxfloat

BEGIN

IF @first IS NULL OR @second IS NULL

RETURN NULL

IF @first < @second

RETURN @first

RETURN @second

END

So the full statement is:

CREATE FUNCTION oracle.xxxfloat (@first AS FLOAT, @second AS FLOAT)

RETRNS FLOAT

BEGIN

IF @first IS NULL OR @second IS NULL

RETURN NULL

IF @first < @second

RETURN @first

RETURN @second

END

This reply was modified 1 month ago by stevec883.

Jonathan AC Roberts SSCoach Points: 18400 More actions · Answer 9

Create a new schema:

IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'oracle')
    EXEC('CREATE SCHEMA oracle;');
GO

Create the function:

GO
CREATE OR ALTER FUNCTION oracle.xxxfloat (@first FLOAT, @second FLOAT)
RETURNS FLOAT
AS
BEGIN
    DECLARE @result FLOAT;

    IF @first IS NULL OR @second IS NULL
        RETURN NULL;

    IF @first < @second
        SET @result = @first;
    ELSE
        SET @result = @second;

    RETURN @result;
END;
GO

Test it:

-- Test Cases
SELECT oracle.xxxfloat(10.5, 20.3) AS Result1;  -- Expected: 10.5
SELECT oracle.xxxfloat(25.7, 18.4) AS Result2;  -- Expected: 18.4
SELECT oracle.xxxfloat(-5.2, -10.8) AS Result3; -- Expected: -10.8
SELECT oracle.xxxfloat(7.9, 7.9) AS Result4;    -- Expected: 7.9
SELECT oracle.xxxfloat(NULL, 5.5) AS Result5;   -- Expected: NULL
SELECT oracle.xxxfloat(12.3, NULL) AS Result6;  -- Expected: NULL
SELECT oracle.xxxfloat(NULL, NULL) AS Result7;  -- Expected: NULL

Results:

Screenshot 2025-02-25 194715

stevec883 Mr or Mrs. 500 Points: 500 More actions · Answer 10

Wow, thanks Jonathan. That works perfectly with a small exception that I have decimals in column 2. But the result set for column2 is correct, except for the 0's, exactly 6 zeros to the right of the decimal for each correct number. Perhaps I need a slight adjustment in the math(?). 0 has a result of 0.000000, 96 has a result of 96.000000, etc. It could even be a mistake on my end transfering your arrangement. Wow, you're good!

In any case, I'm on the right path, so thanks a ton!

This reply was modified 1 month ago by stevec883.
This reply was modified 1 month ago by stevec883.

EdVassie SSC Guru Points: 60502 More actions · Answer 11

Once you have a function that works, include the WITH SCHEMABINDING clause in the function definition. It will run much faster.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

After an Oracle migration, write out a function into a View SELECT statement

Cookies on SQLServerCentral