Skip to Content
-1

convert oracle if statement to hana

Dec 20, 2017 at 11:30 AM

114

avatar image

Hi Experts,

Here I have one requirement to convert oracle procedure to hana

this is example oracle code:

 Create Procedure "KABIL_PRACTICE"."IF_TEST"
 as
 begin
 if F_KEEP_DATE (CC_DATE,Current_date) = 'T'
 then
...sql statements...
 else 
...sql statements...;
 End if;
 end;

how can I convert it into hana, I don't know the concept of comma(,) in a if statement .

Any suggestions

10 |10000 characters needed characters left characters exceeded

What you have tried so far and what issues you have?

From my point of view you can solve that issue by yourself just checking the official SQL Script Guide in which the syntax of SQLScript including imperative control structures like IF are described.

Regarding the "comma" value. That is is just a parameter separator for the called F_KEEP_DATE function.

Regards,
Florian

0

hi Florian Pfeffer

thanks for your reply...

now only i came to know F_KEEP_DATE is a function

CODE - ORACLE:

create FUNCTION F_KEEP_DATE(IN TDATE DATE,IN TTODAY DATE) RETURN CHAR IS
RETVALUE CHAR;
/******************************************************************************
NAME: F_KEEP_DATE
PURPOSE: RETURNS T/F WHETHER OR NOT THE DATE MEETS THE CRITERIA TO MAINTAIN DATA.
REVISIONS:
VER DATE AUTHOR DESCRIPTION
--------- ---------- --------------- ------------------------------------
1.0 6/18/2008 1. CREATED THIS FUNCTION.
******************************************************************************/
BEGIN
RETVALUE := 'F';
IF (TDATE BETWEEN TRUNC(TTODAY - 30) AND TRUNC(TTODAY)) OR (TO_CHAR(TDATE+1,'MM') <> TO_CHAR(TDATE,'MM')) THEN -- KEEP
RETVALUE := 'T';
END IF;
RETURN RETVALUE;
END F_KEEP_DATE;

and I try to convert this function into HANA.. I'm facing an issue with that it throws an error like range comparison is not supported

CODE - HANA:

create FUNCTION "KABIL_PRACTICE"."F_KEEP_DATE"( TDATE DATE, TTODAY DATE) 
RETURNS RETVALUE CHAR
LANGUAGE SQLSCRIPT   
SQL SECURITY INVOKER AS
/******************************************************************************
NAME: F_KEEP_DATE
PURPOSE: RETURNS T/F WHETHER OR NOT THE DATE MEETS THE CRITERIA TO MAINTAIN DATA.
REVISIONS:
VER DATE AUTHOR DESCRIPTION
--------- ---------- --------------- ------------------------------------
1.0 6/18/2008 1. CREATED THIS FUNCTION.
******************************************************************************/
BEGIN
RETVALUE := 'F';
IF :TDATE BETWEEN ADD_DAYS (TO_DATE (:TTODAY, 'YYYY-MM-DD'), -30) AND :TTODAY 
OR  TO_CHAR( EXTRACT (MONTH FROM ADD_DAYS (TO_DATE (:TDATE, 'YYYY-MM-DD'), 1)))<> 
TO_CHAR( EXTRACT (MONTH FROM TO_DATE (TDATE, 'YYYY-MM-DD')))
THEN 
RETVALUE := 'T';
END IF;
--RETURN :RETVALUE;
END ;

waiting for some suggestions from experts...

thank you...

0
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Lars Breddemann
Dec 20, 2017 at 11:06 PM
0

I suggest stopping copy&paste code that you don't understand and trying to get it through the compiler without error.

Instead, invest the time and effort to figure out, what the code is supposed to do.

What is the functionality of this function? Do you need all those type conversions to achieve this functionality?

How would you write a function in SAP HANA that provides the same functionality?

Maybe it would be a good idea to clean up the code and understand it step by step!

create FUNCTION "KABIL_PRACTICE"."F_KEEP_DATE"( TDATE DATE, TTODAY DATE) 
RETURNS RETVALUE CHAR
LANGUAGE SQLSCRIPT   
SQL SECURITY INVOKER AS
/******************************************************************************
NAME: F_KEEP_DATE
PURPOSE: RETURNS T/F WHETHER OR NOT THE DATE MEETS THE CRITERIA TO MAINTAIN DATA.
REVISIONS:
VER DATE AUTHOR DESCRIPTION
--------- ---------- --------------- ------------------------------------
1.0 6/18/2008 1. CREATED THIS FUNCTION.
******************************************************************************/
BEGIN
RETVALUE := 'F';
IF :TDATE BETWEEN ADD_DAYS (TO_DATE (:TTODAY, 'YYYY-MM-DD'), -30) AND :TTODAY 
OR  TO_CHAR( EXTRACT (MONTH FROM ADD_DAYS (TO_DATE (:TDATE, 'YYYY-MM-DD'), 1)))<> 
TO_CHAR( EXTRACT (MONTH FROM TO_DATE (TDATE, 'YYYY-MM-DD')))
THEN 
RETVALUE := 'T';
END IF;
--RETURN :RETVALUE;
END ;

First, remove the useless fluff of comments. The change history is captured in the code repository, so keeping a log of changes is pointless here. (BTW: you really should be using repository objects when developing anything that you like to deploy to a different system one day.)

create FUNCTION "KABIL_PRACTICE"."F_KEEP_DATE"( TDATE DATE, TTODAY DATE) 
RETURNS RETVALUE CHAR
LANGUAGE SQLSCRIPT   
SQL SECURITY INVOKER AS
BEGIN
RETVALUE := 'F';
IF :TDATE BETWEEN ADD_DAYS (TO_DATE (:TTODAY, 'YYYY-MM-DD'), -30) AND :TTODAY 
OR  TO_CHAR( EXTRACT (MONTH FROM ADD_DAYS (TO_DATE (:TDATE, 'YYYY-MM-DD'), 1)))<> 
TO_CHAR( EXTRACT (MONTH FROM TO_DATE (TDATE, 'YYYY-MM-DD')))
THEN 
RETVALUE := 'T';
END IF;
END;

Next, look at what the function actually does. It's a single IF statement, so the job of this function is to encapsulate the condition check. Let's focus on that. What are the conditions checked here?

   :TDATE BETWEEN ADD_DAYS (TO_DATE (:TTODAY, 'YYYY-MM-DD'), -30) AND :TTODAY 
OR 
TO_CHAR( EXTRACT (MONTH FROM ADD_DAYS (TO_DATE (:TDATE, 'YYYY-MM-DD'), 1))) <> 
TO_CHAR( EXTRACT (MONTH FROM TO_DATE (TDATE, 'YYYY-MM-DD'))) 

We see that there are in fact two conditions of which either one needs to be TRUE to make the whole function TRUE. Still, with all the data type conversion in place, it is hard to see, what is being checked here.
So, let's get rid of them, where we can.

   :TDATE BETWEEN ADD_DAYS (:TTODAY, -30) AND :TTODAY 
OR EXTRACT (MONTH FROM ADD_DAYS (:TDATE, 1)) <> 
   EXTRACT (MONTH FROM :TDATE) 

Now, we can see, that the first condition checks whether :TDATE is within the past 30 days.
The second condition checks if the day after :TDATE is still in the same month than :TDATE.
That means, this condition checks if:TDATE is the last day of the month. Summarizing, the function returns 'T' (TRUE) when the provided date is either within the last 30 days or when the provided date is the last day of the month.

Checking the Datetime functions () in SAP HANA we find a couple of useful functions that we can use to make this code clearer.

   :TDATE BETWEEN ADD_DAYS (:TTODAY, -30) AND :TTODAY 
OR :TDATE = LAST_DAY(:TDATE) 

The :TTODAY variable is a bit tricky to remove here - and we should remove it in order to make the function easier to understand.
I see two options here. Either the variable really is used as a flexible reference or anchor date.In this case, it should be renamed to something like :reference_date.
However, I got the suspicion that it really is always used with today's date. This allows us to remove the parameter altogether.
We also should pick a better name for both the parameter and the function itself and change the return value to the more common integer as a substitute for a BOOLEAN.

The original comment indicated that the purpose of the function is to check whether data was still in a time window in which it can be modified. I'm not really happy with 'dataInEditPeriod' but it sure is more obvious than 'F_KEEP_DATE'.
As for the parameter, how about 'checked_day'?

Finally, swap the clumsy 'RETVALUE' for 'result' and we get this:

ccreate FUNCTION "dataInEditPeriod"(checked_day DATE) 
RETURNS result INTEGER
LANGUAGE SQLSCRIPT   
SQL SECURITY INVOKER AS
BEGIN
    result := 0;
    IF     (:checked_day BETWEEN ADD_DAYS (current_date, -30) AND current_date) 
       OR  (:checked_day = LAST_DAY(:checked_day))
    THEN 
        result := 1;
    END IF;
END;

The calling code would then read like this:

Create Procedure "KABIL_PRACTICE"."IF_TEST"
as
begin
    if "dataInEditPeriod" (CC_DATE) = 1
    then
    ...sql statements...
    else 
    ...sql statements...;
    End if;
end;

That's not perfect, but I'd say it is heaps better to understand than your starting position.

Cheers,

Lars

Show 3 Share
10 |10000 characters needed characters left characters exceeded

Hi Lars Breddemann

Thanks for your answer... But it throws an error while at the time of creating function like,

feature not supported: Range comparison is not supported

0

Hi Lars Breddemann,

Between operator is not working in my function

0

Oops, yes, that's correct. The SQL Script IF statement doesn't do BETWEEN (in the current releases).

Just replace this bit by the equivalent range conditions:

create FUNCTION "dataInEditPeriod"(checked_day DATE) 
RETURNS result INTEGER
LANGUAGE SQLSCRIPT   
SQL SECURITY INVOKER AS
BEGIN
    result := 0;
    IF     (:checked_day >= ADD_DAYS (current_date, -30)
            AND :checked_day <= current_date) 
       OR  (:checked_day = LAST_DAY(:checked_day))
    THEN 
        result := 1;
    END IF;
END;
0