Home » SQL & PL/SQL » SQL & PL/SQL » calculation else part (oracle 19c, windows 11 pro)
calculation else part [message #690411] |
Tue, 02 September 2025 02:30  |
 |
hissam78
Messages: 208 Registered: August 2011 Location: PAKISTAN
|
Senior Member |
|
|
Dear Experts,
Following is the function we are using to calculate Over time, if flag v_pf is equal to other than "R" flag,it is working fine, but if v_pf is equal to "R" than else part not calculating as the condition define in Case statement, some one can help us pls.
thanks
create or replace FUNCTION CALC_OT(V_MINS IN NUMBER,V_PF CHAR) RETURN
NUMBER
IS
OT_MINS NUMBER;
BEGIN
IF V_PF <> 'R' THEN
OT_MINS :=
CASE
WHEN V_MINS BETWEEN 0 AND 24 THEN 0
WHEN V_MINS BETWEEN 25 AND 49 THEN 30
WHEN V_MINS BETWEEN 50 AND 60 THEN 60
WHEN V_MINS BETWEEN 61 AND 84 THEN 60
WHEN V_MINS BETWEEN 85 AND 109 THEN 90
WHEN V_MINS BETWEEN 110 AND 120 THEN 120
WHEN V_MINS BETWEEN 121 AND 144 THEN 120
WHEN V_MINS BETWEEN 145 AND 169 THEN 150
WHEN V_MINS BETWEEN 170 AND 180 THEN 180
WHEN V_MINS BETWEEN 181 AND 204 THEN 180
WHEN V_MINS BETWEEN 205 AND 229 THEN 210
WHEN V_MINS BETWEEN 230 AND 240 THEN 240
ELSE 0
END;
-- IF OT_MINS >= 30 THEN
-- RETURN ROUND((OT_MINS-30)/60,2);
--ELSE
RETURN ROUND(OT_MINS/60,2);
--END IF;
ELSE
OT_MINS := V_MINS;
RETURN V_MINS/60;
END IF;
END;
|
|
|
Re: calculation else part [message #690412 is a reply to message #690411] |
Tue, 02 September 2025 02:40   |
John Watson
Messages: 8989 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
What behaviour are you expecting? For me, 21 ELSE 0
22 END;
23 -- IF OT_MINS >= 30 THEN
24 -- RETURN ROUND((OT_MINS-30)/60,2);
25 --ELSE
26 RETURN ROUND(OT_MINS/60,2);
27 --END IF;
28 ELSE
29 OT_MINS := V_MINS;
30 RETURN V_MINS/60;
31 END IF;
32 END;
33 /
Function created.
orclz> select calc_ot(1000,'S') from dual;
CALC_OT(1000,'S')
--------------------
0
orclz> select calc_ot(1000,'R') from dual;
CALC_OT(1000,'R')
--------------------
16.66666666666666667 seems fair enough.
|
|
|
|
|
|
|
|
|
|
Re: calculation else part [message #690421 is a reply to message #690417] |
Wed, 03 September 2025 00:38   |
 |
hissam78
Messages: 208 Registered: August 2011 Location: PAKISTAN
|
Senior Member |
|
|
Dear Expert
following is the scenario, having table with PF status R and P, and also the procedure where we call the above mentioned function. but we are not able to get OT as per Case statement for R Status.it may useful for you to help us. thanks,
CREATE TABLE "TEST_REST"
( "EMP_ID" NUMBER,
"ATTENDANCE_DATE" DATE,
"PF" VARCHAR2(20 BYTE),
"CHECK_IN" TIMESTAMP (6),
"CHECK_OUT" TIMESTAMP (6),
"TIME_IN" TIMESTAMP (6),
"TIME_OUT" TIMESTAMP (6),
"MINUTES" NUMBER,
"WORKING_MINS" NUMBER,
"ACT_OT" NUMBER,
"OT" NUMBER
);
REM INSERTING into TEST_REST
SET DEFINE OFF;
Insert into TEST_REST (EMP_ID,ATTENDANCE_DATE,PF,CHECK_IN,CHECK_OUT,TIME_IN,TIME_OUT,MINUTES,WORKING_MINS,ACT_OT,OT) values (4000008,to_date('29-AUG-25','DD-MON-RR'),'R',to_timestamp('29-AUG-25 07.08.30.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('29-AUG-25 03.14.55.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('29-AUG-25 07.30.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('29-AUG-25 04.00.00.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'),465,510,465,7.75);
Insert into TEST_REST (EMP_ID,ATTENDANCE_DATE,PF,CHECK_IN,CHECK_OUT,TIME_IN,TIME_OUT,MINUTES,WORKING_MINS,ACT_OT,OT) values (4000044,to_date('29-AUG-25','DD-MON-RR'),'R',to_timestamp('29-AUG-25 07.07.13.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('29-AUG-25 06.03.37.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('29-AUG-25 07.00.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('29-AUG-25 03.30.00.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'),656,510,656,10.93333333333333333333333333333333333333);
Insert into TEST_REST (EMP_ID,ATTENDANCE_DATE,PF,CHECK_IN,CHECK_OUT,TIME_IN,TIME_OUT,MINUTES,WORKING_MINS,ACT_OT,OT) values (4000049,to_date('29-AUG-25','DD-MON-RR'),'R',to_timestamp('29-AUG-25 07.29.20.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('29-AUG-25 05.05.25.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('29-AUG-25 08.00.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('29-AUG-25 06.00.00.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'),545,600,545,9.08333333333333333333333333333333333333);
Insert into TEST_REST (EMP_ID,ATTENDANCE_DATE,PF,CHECK_IN,CHECK_OUT,TIME_IN,TIME_OUT,MINUTES,WORKING_MINS,ACT_OT,OT) values (4000081,to_date('29-AUG-25','DD-MON-RR'),'R',to_timestamp('29-AUG-25 07.39.04.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('29-AUG-25 03.33.28.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('29-AUG-25 07.00.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('29-AUG-25 03.30.00.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'),474,510,474,7.9);
Insert into TEST_REST (EMP_ID,ATTENDANCE_DATE,PF,CHECK_IN,CHECK_OUT,TIME_IN,TIME_OUT,MINUTES,WORKING_MINS,ACT_OT,OT) values (4000100,to_date('30-AUG-25','DD-MON-RR'),'P',to_timestamp('30-AUG-25 07.19.20.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('30-AUG-25 06.38.18.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('30-AUG-25 07.30.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('30-AUG-25 04.00.00.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'),668,510,158,2.5);
Insert into TEST_REST (EMP_ID,ATTENDANCE_DATE,PF,CHECK_IN,CHECK_OUT,TIME_IN,TIME_OUT,MINUTES,WORKING_MINS,ACT_OT,OT) values (4000136,to_date('30-AUG-25','DD-MON-RR'),'P',to_timestamp('30-AUG-25 06.26.09.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('30-AUG-25 07.09.48.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('30-AUG-25 07.00.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('30-AUG-25 03.30.00.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'),730,510,220,3.5);
Insert into TEST_REST (EMP_ID,ATTENDANCE_DATE,PF,CHECK_IN,CHECK_OUT,TIME_IN,TIME_OUT,MINUTES,WORKING_MINS,ACT_OT,OT) values (4000341,to_date('30-AUG-25','DD-MON-RR'),'P',to_timestamp('30-AUG-25 08.12.37.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('30-AUG-25 06.06.07.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('30-AUG-25 08.00.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('30-AUG-25 04.30.00.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'),594,510,84,1);
Insert into TEST_REST (EMP_ID,ATTENDANCE_DATE,PF,CHECK_IN,CHECK_OUT,TIME_IN,TIME_OUT,MINUTES,WORKING_MINS,ACT_OT,OT) values (4000507,to_date('30-AUG-25','DD-MON-RR'),'P',to_timestamp('30-AUG-25 07.14.19.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('30-AUG-25 07.12.04.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('30-AUG-25 07.00.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('30-AUG-25 03.30.00.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'),718,510,208,3.5);
Insert into TEST_REST (EMP_ID,ATTENDANCE_DATE,PF,CHECK_IN,CHECK_OUT,TIME_IN,TIME_OUT,MINUTES,WORKING_MINS,ACT_OT,OT) values (4000677,to_date('30-AUG-25','DD-MON-RR'),'P',to_timestamp('30-AUG-25 07.04.46.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('30-AUG-25 05.22.35.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('30-AUG-25 07.00.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('30-AUG-25 03.30.00.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'),618,510,108,1.5);
create or replace PROCEDURE UPDATE_OT(V_UCODE IN NUMBER,V_D1 IN DATE,V_D2 IN DATE, V_EMP_ID IN NUMBER DEFAULT NULL)
is
CURSOR C1 IS
SELECT *
FROM TEST_REST
WHERE UCODE = V_UCODE
AND (V_EMP_ID IS NULL OR EMP_ID = V_EMP_ID)
AND ATTENDANCE_DATE BETWEEN V_D1 AND V_D2;
V_MINS NUMBER;
BEGIN
FOR I IN C1
LOOP
if (cast(I.check_in as date) < cast(I.time_in as date) ) then
V_MINS := ROUND((CAST(I.CHECK_OUT AS DATE) - CAST(I.TIME_IN AS DATE)) * 60 *24,0);
else
V_MINS := ROUND((CAST(I.CHECK_OUT AS DATE) - CAST(I.CHECK_IN AS DATE)) * 60 *24,0);
end if;
IF (I.PF IN ('R')) THEN
I.ACT_OT := V_MINS;
I.OT := CALC_OT(I.ACT_OT,I.PF);
elsif I.ALLOW_OT = 'ALLOW' THEN
I.ACT_OT := V_MINS - I.WORKING_MINS;
I.OT := CALC_OT(I.ACT_OT,I.PF);
elsif (V_MINS > I.WORKING_MINS ) and I.ALLOW_OT = 'ALLOW(REST+HOLIDAY)' AND I.PF IN ('R') THEN
I.ACT_OT := V_MINS - I.WORKING_MINS;
I.OT := CALC_OT(I.ACT_OT,I.PF);
ELSE
I.OT := 0;
I.ACT_OT := 0;
END IF;
UPDATE TEST_REST SET MINUTES = V_MINS WHERE EMP_ID = I.EMP_ID AND ATTENDANCE_DATE = I.ATTENDANCE_DATE;
UPDATE TEST_REST SET ACT_OT = I.ACT_OT WHERE EMP_ID = I.EMP_ID AND ATTENDANCE_DATE = I.ATTENDANCE_DATE;
UPDATE TEST_REST SET OT = I.OT WHERE EMP_ID = I.EMP_ID AND ATTENDANCE_DATE = I.ATTENDANCE_DATE;
COMMIT;
DBMS_OUTPUT.PUT_LINE('DATE IS ' || I.ATTENDANCE_DATE || ' MINS' || V_MINS || ' OT ' || I.OT);
END LOOP;
END;
[Updated on: Wed, 03 September 2025 00:46] Report message to a moderator
|
|
|
|
|
Re: calculation else part [message #690424 is a reply to message #690422] |
Wed, 03 September 2025 03:22   |
cookiemonster
Messages: 13972 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Some notes on your procedure:
create or replace PROCEDURE UPDATE_OT(V_UCODE IN NUMBER,V_D1 IN DATE,V_D2 IN DATE, V_EMP_ID IN NUMBER DEFAULT NULL)
is
V_MINS NUMBER;
BEGIN
FOR I IN (SELECT *
FROM TEST_REST
WHERE UCODE = V_UCODE
AND (V_EMP_ID IS NULL OR EMP_ID = V_EMP_ID)
AND ATTENDANCE_DATE BETWEEN V_D1 AND V_D2) LOOP
--the IF you had here was pointless as the IF and the else did the same thing
V_MINS := ROUND((CAST(I.CHECK_OUT AS DATE) - CAST(I.TIME_IN AS DATE)) * 60 *24,0);
IF (I.PF IN ('R')) THEN
I.ACT_OT := V_MINS;
I.OT := CALC_OT(I.ACT_OT,I.PF);
elsif I.ALLOW_OT = 'ALLOW' THEN
I.ACT_OT := V_MINS - I.WORKING_MINS;
I.OT := CALC_OT(I.ACT_OT,I.PF);
/* This ELSIF will never run as it's a subset of the initial IF. If you want it to run then it needs to go first.
elsif (V_MINS > I.WORKING_MINS ) and I.ALLOW_OT = 'ALLOW(REST+HOLIDAY)' AND I.PF IN ('R') THEN
I.ACT_OT := V_MINS - I.WORKING_MINS;
I.OT := CALC_OT(I.ACT_OT,I.PF);
*/
ELSE
I.OT := 0;
I.ACT_OT := 0;
END IF;
--Three updates that all update the same row(s) to set different columns is very inefficient, use a single update
UPDATE TEST_REST
SET MINUTES = V_MINS,
ACT_OT = I.ACT_OT,
OT = I.OT
WHERE EMP_ID = I.EMP_ID AND ATTENDANCE_DATE = I.ATTENDANCE_DATE;
--Generally using a commit in a procedure is a bad idea, the code that started the the process should do commits
--doing a commit per row is just asking for problems if you hit an error and it's inefficient.
--COMMIT;
DBMS_OUTPUT.PUT_LINE('DATE IS ' || I.ATTENDANCE_DATE || ' MINS' || V_MINS || ' OT ' || I.OT);
END LOOP;
END;
|
|
|
Re: calculation else part [message #690425 is a reply to message #690423] |
Wed, 03 September 2025 03:26   |
cookiemonster
Messages: 13972 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
hissam78 wrote on Wed, 03 September 2025 09:17Hi Cookiemonster,
we have changed and check the function with all aspect, as Michel Cadot suggested, but not successful to get the results, so it is the same function using as i shared before .
Are you being serious?
You state that you want the CASE statement to apply for status R.
Your function is explicitly written to not do that.
To get it to do that you need to change the function.
If, after changing the function, you're still not getting the results you expect, then there is some other problem, but going back to the original function isn't going to fix it.
|
|
|
|
Re: calculation else part [message #690427 is a reply to message #690426] |
Thu, 04 September 2025 03:28   |
cookiemonster
Messages: 13972 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Here's what your function is currently doing:
BEGIN
OT_MINS := <case statement>
IF V_PF <> 'R' THEN
RETURN ROUND(OT_MINS/60,2);
ELSE
OT_MINS := V_MINS;
RETURN V_MINS/60;
END IF;
END;
So when v_pf is R, the value of ot_mins that was calculated by the case statement is overwritten. If you want to use the result of the CASE regardless of the value of v_pf you have to write code that actually does that.
|
|
|
|
Goto Forum:
Current Time: Sat Sep 06 05:56:30 CDT 2025
|