Modify procedure calculation for readability [message #666323] |
Sun, 29 October 2017 22:19 |
|
veepee
Messages: 16 Registered: June 2017
|
Junior Member |
|
|
I am using Oracle 11.2.0.4 and here's a code snippet inside a stored procedure. The procedure calc_computr_data has a few calculations (inside the WITH clause) for a INSERT statement.
How I could make the calculations happen, perhaps in a earlier procedure, so that code looks more readable. This procedure/INSERT statement will then reference the values instead of calculations.
Any suggestions on how this can be done to make the code more readable is highly appreciated.
PROCEDURE INIT_VARIABLES
-- some initialization and variables
--
END init_variables;
PROCEDURE calc_computr_data(p_var1 IN NUMBER,
p_var2 IN date%TYPE,
p_var3 IN date%TYPE) AS
BEGIN
INSERT INTO rp_rac_cltr_extract
(col1,
col2,
....,
....,
colN)
WITH opdays AS
(select opday as opdaystart,
((opday + 1) - 1 / 86400) as opdaystop,
FirstDayStarttime,
((trunc(FirstDayStarttime) + 1) - 1 / 86400) as FirstDayStoptime,
trunc(LastdayStoptime) as LastdayStarttime,
LastdayStoptime
from (select (trunc(x.Day_in_Qtr, 'Q') + y.dateoffset) as opday,
trunc(x.Day_in_Qtr, 'Q') as FirstDayStarttime,
(((add_months(trunc(x.Day_in_Qtr, 'Q'), 3) - 1) + 1) -
1 / 86400) as LastdayStoptime
from (select p_var2 as Day_in_Qtr
from dual
) X
cross join (SELECT (LEVEL - 1) as dateoffset
FROM dual
CONNECT BY LEVEL <= 10000
) Y
--
) Z
where Z.opday <= Z.LastdayStoptime)
SELECT col1,
col2,
...
from table
where some_condition 1 = some_statement;
|
|
|
|
Re: Modify procedure calculation for readability [message #666326 is a reply to message #666323] |
Mon, 30 October 2017 01:42 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
To make it more readable, you need to add some comments. I can work out what you are doing, but why should I have to work it out when you could just tell me? As you comment each line, you will see how to simplify it. For example, if you add a comment like this:
((trunc(FirstDayStarttime) + 1) - 1 / 86400) as FirstDayStoptime, --the last second of the FirstDay: strip the time off FirstDayStarttime, add a day, take off a second and then this:
trunc(x.Day_in_Qtr, 'Q') as FirstDayStarttime, --the FirstDayStarttime is first day of quarter, with the time stripped off
it becomes obvious that you don't need the TRUNC because you have already done it.
|
|
|
Re: Modify procedure calculation for readability [message #666337 is a reply to message #666326] |
Mon, 30 October 2017 08:12 |
|
veepee
Messages: 16 Registered: June 2017
|
Junior Member |
|
|
Thank you John and BlackSwan! Here are the comments to the calculations.
(select opday as opdaystart, -- start date of quarter (for p_var2 value of 10/30/2017 this value would be 10/1/2017)
((opday + 1) - 1 / 86400) as opdaystop, -- first day with the timestamp (for p_var2 value of 10/30/2017 this value would be 10/1/2017 11:59:59 PM)
FirstDayStarttime, -- First Day of quarter without timestamp (for p_var2 value of 10/30/2017 this value would be 10/1/2017)
((trunc(FirstDayStarttime) + 1) - 1 / 86400) as FirstDayStoptime, -- Stop Time of first day in a quarter minus a second
trunc(LastdayStoptime) as LastdayStarttime, -- Last Day of quarter minus the timestamp
LastdayStoptime -- last day of the quarter with timestamp (for p_var2 value of 10/30/2017 this date would be 12/31/2017 11:59:59 PM)
from (select (trunc(x.Day_in_Qtr, 'Q') + y.dateoffset) as opday,
trunc(x.Day_in_Qtr, 'Q') as FirstDayStarttime, -- First day of quarter without the timestamp
(((add_months(trunc(x.Day_in_Qtr, 'Q'), 3) - 1) + 1) -
1 / 86400) as LastdayStoptime -- Last day of the quarter with timestamp (for p_var2 of 10/30/2017 the result = 12/31/2017 11:59:59 PM)
from (select p_var2 as Day_in_Qtr -- parameter read in from procedure
from dual
) X
cross join (SELECT (LEVEL - 1) as dateoffset
FROM dual
CONNECT BY LEVEL <= 10000
) Y
--
) Z
where Z.opday <= Z.LastdayStoptime
I was thinking along the lines of the calculations done in a earlier procedure and assign each calculation to a global variable. Like below
gv_firstdaystarttime := trunc(p_opday,'Q');
gv_firstdaystoptime := (trunc(trunc(p_opday,'Q')) + 1 ) - 1/86400;
Any thoughts on the same..
[Updated on: Mon, 30 October 2017 08:20] Report message to a moderator
|
|
|
Re: Modify procedure calculation for readability [message #666343 is a reply to message #666337] |
Mon, 30 October 2017 09:23 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Unless you're going to use those variables somewhere other than this SQL then there's no point.
If they're going to be used in multiple places in the procedure then you probably should have done that already.
And as John already pointed out - you don't need two truncs on firstdaystoptime.
Also I wouldn't bracket the way you are. Operator precedence works in your favour, but I would always bracket 1/86400. I wouldn't bracket an entire line.
So this:
(((add_months(trunc(x.Day_in_Qtr, 'Q'), 3) - 1) + 1) -
1 / 86400) as LastdayStoptime
Should be:
add_months(trunc(x.Day_in_Qtr, 'Q'), 3) - (1 / 86400) as LastdayStoptime
(adding then subtracting 1 is also pointless)
or even:
add_months(trunc(x.Day_in_Qtr, 'Q'), 3) - (1 /24/60/60) as LastdayStoptime
I personally find that more obvious but that's probably a matter of taste.
|
|
|
|
|
Re: Modify procedure calculation for readability [message #666358 is a reply to message #666357] |
Tue, 31 October 2017 16:01 |
|
veepee
Messages: 16 Registered: June 2017
|
Junior Member |
|
|
The below select statement captures the variable from a procedure input. How do we modify the select statement to make it dynamically populate from within a range of firstdaystarttime and lastdaystarttime?
select p_var2 as day_in_qtr from dual -- parameter read in from procedure
Desired result for a p_var2 value of 10/31/2017 is below:
OPDAYSTART OPDAYSTOP FIRSTDAYSTARTTIME FIRSTDAYSTOPTIME LASTDAYSTARTTIME LASTDAYSTOPTIME
10/1/2017 10/1/2017 11:59:59pm 10/1/2017 10/1/2017 11:59:59PM 12/31/2017 12/31/2017 11:59:59 PM
10/2/2017 10/2/2017 11:59:59PM 10/1/2017 10/1/2017 11:59:59PM 12/31/2017 12/31/2017 11:59:59 PM
10/3/2017 10/3/2017 11:59:59PM 10/1/2017 10/1/2017 11:59:59PM 12/31/2017 12/31/2017 11:59:59 PM
...
..
12/31/2017 12/31/2017 11:59:59PM 10/1/2017 10/1/2017 11:59:59 PM 12/31/2017 12/31/2017 11:59:59 PM
[Updated on: Tue, 31 October 2017 16:02] Report message to a moderator
|
|
|
|
|
|
|
|
|
|
|
|
Re: Modify procedure calculation for readability [message #666408 is a reply to message #666396] |
Thu, 02 November 2017 16:44 |
|
veepee
Messages: 16 Registered: June 2017
|
Junior Member |
|
|
I posted the complete query which results in duplicate when using below CONNECT
CONNECT BY LEVEL <= trunc(LastdayStoptime) - trunc(FirstdayStarttime) + 1
but works when the below WHERE clause is included after the alias Z in the original code
...
....
) Z
WHERE Z.opday <= Z.LastdayStoptime);
Here's the complete code ..
PROCEDURE INIT_VARIABLES is
BEGIN
gv_firstdaystarttime := trunc(to_date(sysdate, 'MM/DD/YYYY'), 'Q');
gv_firstdaystoptime := (trunc(to_date(sysdate, 'MM/DD/YYYY'), 'Q') + 1) -
(1 / 86400);
gv_lastdaystarttime := add_months(trunc(to_date(sysdate,
'MM/DD/YYYY HH24:MI:SS'),
'Q'),
3) - (1 / 24 / 60 / 60);
gv_lastdaystoptime := add_months(trunc(p_opday, 'Q'), 3) - (1 / 86400);
END init_variables;
PROCEDURE calc_lschannelcut_data(p_execution_id IN NUMBER,
p_partition_key IN rp_rec_gen_extract.partition_key%TYPE,
p_opday IN statementschedule.operatingdate%TYPE,
p_backout IN VARCHAR2 := NULL,
p_input_start IN DATE := NULL,
p_input_stop IN DATE := NULL) AS
INSERT INTO rp_rac_cltr_extract
(Col1,
Col2,
Col3,
Col4,
...
Coln)
WITH opdays AS
(select opday as opdaystart,
(opday + 1) - (1/24/60/60) as opdaystop, -- Add 1 day to operating day and take off a second to get operating day stop time with timestamp
FirstDayStarttime, -- This is the date without the timestamp from the beginning of quarter basing on the random date chosen
(FirstDayStarttime + 1) - (1/24/60/60) as FirstDayStoptime, -- get firstdaystarttime + add a day - subtract 1 second to get the FIRSTDAYSTOPTIME
trunc(LastdayStoptime) as LastdayStarttime, -- remove timestamp from LastDayStopTime to get LastDayStartTime
LastdayStoptime
from (select (trunc(x.Day_in_yr, 'Q') + y.dateoffset) as opday,
trunc(x.Day_in_yr, 'Q') as FirstDayStarttime, -- First day of quarter without the timestamp
(((add_months(trunc(x.Day_in_yr, 'Q'), 3) - 1) + 1) -
1 / 86400) as LastdayStoptime -- Last day of the quarter with timestamp (for p_var2 of 10/30/2017 the result = 12/31/2017 11:59:59 PM)
from (
--
select to_date('11/1/2017 00:00:00', 'mm/dd/yyyy hh24:mi:ss') as day_in_yr -- This could be any date but between firstdaystarttime and lastdaystarttime
from dual -- Change this date if you want to run for a different Period
--
) X
cross join (SELECT (LEVEL - 1) as dateoffset
FROM dual
CONNECT BY LEVEL <= 1000 -- Get 1000 dateoffset in case period is large
) Y
--
) Z
CONNECT BY LEVEL <= trunc(LastdayStoptime) - trunc(FirstdayStarttime) + 1
)
[Updated on: Thu, 02 November 2017 16:44] Report message to a moderator
|
|
|