Home » SQL & PL/SQL » SQL & PL/SQL » Modify procedure calculation for readability (Oracle 11.2.0.4)
Modify procedure calculation for readability [message #666323] Sun, 29 October 2017 22:19 Go to next message
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 #666324 is a reply to message #666323] Sun, 29 October 2017 22:37 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
can you CREATE VIEW?
Re: Modify procedure calculation for readability [message #666326 is a reply to message #666323] Mon, 30 October 2017 01:42 Go to previous messageGo to next message
John Watson
Messages: 8922
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 #666349 is a reply to message #666337] Mon, 30 October 2017 12:03 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Quote:
gv_firstdaystoptime := (trunc(trunc(p_opday,'Q')) + 1 ) - 1/86400;
What comment would you put on this? One that will explain why you have two TRUNC calls? I find that commenting code makes me think about it.
Re: Modify procedure calculation for readability [message #666357 is a reply to message #666349] Tue, 31 October 2017 15:28 Go to previous messageGo to next message
veepee
Messages: 16
Registered: June 2017
Junior Member
Yes, I had that correction to be made on the trunc

[Updated on: Tue, 31 October 2017 15:31]

Report message to a moderator

Re: Modify procedure calculation for readability [message #666358 is a reply to message #666357] Tue, 31 October 2017 16:01 Go to previous messageGo to next message
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 #666359 is a reply to message #666358] Tue, 31 October 2017 18:04 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
If nothing else you needs to learn the following lesson.
NEVER store a date in VARCHAR2.
NEVER store a date in NUMBER.
Only store date in DATE datatype!
Re: Modify procedure calculation for readability [message #666366 is a reply to message #666359] Wed, 01 November 2017 06:20 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Change the connect by to:
connect by level <= end_Date - start_date.

By the way - for your existing query 10000 is massive over-kill, 100 will be enough to cover any quarter.
Re: Modify procedure calculation for readability [message #666367 is a reply to message #666359] Wed, 01 November 2017 06:21 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Where did you get the idea that OP is storing dates not as dates from?
Re: Modify procedure calculation for readability [message #666368 is a reply to message #666367] Wed, 01 November 2017 07:07 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
cookiemonster wrote on Wed, 01 November 2017 04:21
Where did you get the idea that OP is storing dates not as dates from?
>Desired result for a p_var2 value of 10/31/2017 is below:
Re: Modify procedure calculation for readability [message #666372 is a reply to message #666368] Wed, 01 November 2017 07:18 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Quote:

PROCEDURE calc_computr_data(p_var1 IN NUMBER,
p_var2 IN date%TYPE,
Re: Modify procedure calculation for readability [message #666373 is a reply to message #666366] Wed, 01 November 2017 09:55 Go to previous messageGo to next message
veepee
Messages: 16
Registered: June 2017
Junior Member
Although this query is for a QTR. Its only a sample version. The final SQL needs to be tweaked or changed to run for a longer period (6 months OR 1 year at most, and to be decided by business). I used a big arbitrary value of 10000 (though 1000 would have looked a bit more realistic) to factor in the unprecedented request for a larger time window.


As for the restriction using the connect
connect by level <= end_Date - start_date 

is this already handled in the below where clause ??
where Z.opday <= Z.LastdayStoptime
Re: Modify procedure calculation for readability [message #666374 is a reply to message #666373] Wed, 01 November 2017 10:07 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you have the connect by return the exact number of rows you need then you don't need to do the restriction in the where clause.
Thinking about it it should be:
connect by level <= trunc(end_Date) - trunc(start_date ) + 1
Re: Modify procedure calculation for readability [message #666379 is a reply to message #666374] Wed, 01 November 2017 12:23 Go to previous messageGo to next message
veepee
Messages: 16
Registered: June 2017
Junior Member
I tested using the connect by level statement in the query..

CONNECT BY LEVEL <= trunc(LastdayStoptime) - trunc(FirstdayStarttime) + 1

This results multiple occurrences for the first opday (in this case 10/1/2017)


I am thinking the where clause would need to be in the query.
[code
WHERE Z.opday <= Z.LastdayStoptime);
[/code]


Re: Modify procedure calculation for readability [message #666396 is a reply to message #666379] Thu, 02 November 2017 04:41 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
It's hard to know what you did wrong unless you show use the entire query.
Re: Modify procedure calculation for readability [message #666408 is a reply to message #666396] Thu, 02 November 2017 16:44 Go to previous message
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

Previous Topic: to_char date conversion not working
Next Topic: ORA-06502: PL/SQL: numeric or value error.
Goto Forum:
  


Current Time: Thu Mar 28 13:12:11 CDT 2024