Use loop to run sql twice only on a specific date

[sxschech]

Not familiar with writing PL/SQL code and would like to use a loop to eliminate doubling up of an SQL statement in a procedure. The logic is:

-- today <= fall census date + 1    
if trunc(sysdate) <= trunc(fall_census + 1) then
   v_term   := substr(current_term,1,4);
   v_weekno := v_term || lpad(trunc((trunc(sysdate) - trunc(last_fall_census))/7), 2, 0);

   if substr(v_weekno,5) between 0 and 21 then
       v_sem := 20;        -- want 20, 30, 40
   else
       v_sem := 30;        -- want 30, 40
   end if;

insert into histstats

Select a.*  
FROM 
(....
 ....
WHERE substr(term,1,4) =  v_term
AND   substr(term,5,2) >= v_sem
...
...
)  a
;

commit;
end if;

-- today >= fall census date + 1
if trunc(sysdate) >= trunc(fall_census + 1) then
   v_term   := substr(current_term,1,4) + 1;
   v_weekno := v_term || lpad(trunc((trunc(sysdate) - trunc(fall_census))/7), 2, 0);

   if substr(v_weekno,5) between 0 and 21 then
       v_sem := 20;        -- want 20, 30, 40
   else
       v_sem := 30;        -- want 30, 40
   end if;


insert into histstats

Select a.*  
FROM 
(....
 ....
WHERE substr(term,1,4) =  v_term
AND   substr(term,5,2) >= v_sem
 ...
 ...
)  a
;

commit;
end if;

Since the sql insert statement is the same in both, only the what is returned by the variables differ, I would like to figure out how to modify the above so that it runs the insert statement twice and with the slightly adjusted variables only on the day when the condition is true. Rather than having to have the insert statement written out twice in the script as is currently the case. One of the reasons would like to have only one insert statement, is that when making changes to the query, only have to do it once rather than twice and risk possibly missing something.

Thanks.

 

[Dagon]

The easiest thing would be to put the insert statement inside a procedure. If your code is a stored package, this should be very easy. Even if it's just a block of PL/SQL, it's possible e.g.

declare
  l_a varchar2(20);
  procedure insert_a (p_a in varchar2) is
  begin
    insert into tab (a) values (p_a);
  end;
begin
  l_a := 'X';
  insert_a(l_a);
  l_a := 'Y';
  insert_a(l_a);
  commit;
end;

Retired (not by choice) Oracle contractor.

 

[sxschech]

Hi Dagon,

Thanks for your reply. The code I provided is actually from inside a procedure. I only broke out the relevant portion so as not to take up too much space in the post. Unfortunately, I don't quite follow your example. It looks like there are several insert statements going on. I only need one insert statement, it is only when the condition for the census date matches both criteria

trunc(sysdate) <= trunc(fall_census + 1)
and
trunc(sysdate) >= trunc(fall_census + 1)

would the insert statement need to be run a second time, using the slightly different parameters.

I would like to know how to write the syntax for this psuedo code:

if trunc(sysdate) <= trunc(fall_census + 1) and trunc(sysdate) >= trunc(fall_census + 1) then
   LOOPCount = 2
Else
   LOOPCount = 1
End

if trunc(sysdate) <= trunc(fall_census + 1) then
   v_term   := substr(current_term,1,4);
   v_weekno := v_term || lpad(trunc((trunc(sysdate) - trunc(last_fall_census))/7), 2, 0);

   if substr(v_weekno,5) between 0 and 21 then
       v_sem := 20;        -- want 20, 30, 40
   else
       v_sem := 30;        -- want 30, 40
   end if;

For I = 1 to LOOPCount


If LOOPCount = 2 Then
-- today >= fall census date + 1
--if trunc(sysdate) >= trunc(fall_census + 1) then
   v_term   := substr(current_term,1,4) + 1;
   v_weekno := v_term || lpad(trunc((trunc(sysdate) - trunc(fall_census))/7), 2, 0);

   if substr(v_weekno,5) between 0 and 21 then
       v_sem := 20;        -- want 20, 30, 40
   else
       v_sem := 30;        -- want 30, 40
   end if;
End If;



insert into histstats

Select a.*  
FROM
(....
 ....
WHERE substr(term,1,4) =  v_term
AND   substr(term,5,2) >= v_sem
 ...
 ...
)  a
;
Commit;

Next I

Let's say fall_census is 01-APR-2010
And today is 08-MAR-2010

Running the procedure today, the script will need to run the Insert SQL statement only once using this set of parameters:

if trunc(sysdate) <= trunc(fall_census + 1) then
   v_term   := substr(current_term,1,4);
   v_weekno := v_term || lpad(trunc((trunc(sysdate) - trunc(last_fall_census))/7), 2, 0);

   if substr(v_weekno,5) between 0 and 21 then
       v_sem := 20;        -- want 20, 30, 40
   else
       v_sem := 30;        -- want 30, 40
   end if;

Ok, now it is 01-APR-2010.

The code will run the insert statement using the above parameters. Once that has completed it needs to change the parameters to what is below and rerun the insert statement with the new parameter set.

-- today >= fall census date + 1
if trunc(sysdate) >= trunc(fall_census + 1) then
   v_term   := substr(current_term,1,4) + 1;
   v_weekno := v_term || lpad(trunc((trunc(sysdate) - trunc(fall_census))/7), 2, 0);

   if substr(v_weekno,5) between 0 and 21 then
       v_sem := 20;        -- want 20, 30, 40
   else
       v_sem := 30;        -- want 30, 40
   end if;

 

[Dagon]

I thought you just meant you wanted to avoid having to put in the code for the insert statement twice.

It's difficult to say for certain without seeing all the code but, as far as I can tell, the only difference between the two branches seems to be in the calculation of v_weekno, from which v_sem is derived. You should be able to do something like calculate the minimum value of v_sem and then use that in your insert statement e.g.

if trunc(sysdate) <= trunc(fall_census + 1)
...
   if substr(v_weekno,5) between 0 and 21 then
       v_sem1 := 20;        -- want 20, 30, 40
   else
       v_sem1 := 30;        -- want 30, 40
   end if;

if trunc(sysdate) >= trunc(fall_census + 1)
...
  if substr(v_weekno,5) between 0 and 21 then
       v_sem2 := 20;        -- want 20, 30, 40
   else
       v_sem2 := 30;        -- want 30, 40
   end if;
end if;

v_sem := least(v_sem1, v_sem2);

Select a.*  
FROM
(....
 ....
WHERE substr(term,1,4) =  v_term
AND   substr(term,5,2) >= v_sem
 ...
 ...
)  a
;

However, that is without seeing all the code so there may be more to it than that.




Retired (not by choice) Oracle contractor.

 

[carp]

I believe Dagon's suggestion is pointing you towards where you want to go. To be a little more explicit:

BEGIN
.
.
.
   PROCEDURE load_table(p_term IN VARCHAR2, 
                        p_weekno IN VARCHAR2) IS
      v_sem INTEGER;
   BEGIN
      if substr(p_weekno,5) between 0 and 21 then
          v_sem := 20;        -- want 20, 30, 40
      else
          v_sem := 30;        -- want 30, 40
      end if;
      -- HERE IS THE INSERT/QUERY TO MAINTAIN IN 1 PLACE
      insert into histstats
      Select a.*  
             FROM (........
             WHERE substr(term,1,4) =  p_term
               AND   substr(term,5,2) >= v_sem
              ...
              ...) a;
      COMMIT;
   END;

-- today <= fall census date + 1    
if trunc(sysdate) <= trunc(fall_census + 1) then
   v_term   := substr(current_term,1,4);
   v_weekno := v_term || lpad(trunc((trunc(sysdate) - trunc(last_fall_census))/7), 2, 0);
LOAD_TABLE(v_term, v_weekno)
end if;
-- today >= fall census date + 1
if trunc(sysdate) >= trunc(fall_census + 1) then
   v_term   := substr(current_term,1,4) + 1;
   v_weekno := v_term || lpad(trunc((trunc(sysdate) - trunc(fall_census))/7), 2, 0);
LOAD_TABLE(v_term, v_weekno)
end if;

At least, I think this is what you're asking for.

 

[sxschech]

I'm sorry if I haven't been clear about the objective. Here is the procedure as it currently is up until the Insert Statement:

CREATE OR REPLACE procedure USF_STU.refresh_hist_adm_stats  --USF_STU.refresh_hist_adm_stats
as
pragma autonomous_transaction;

v_weekno        number;
v_term            number;
v_sem            number;
fall_census        date;    
last_fall_census    date;
current_term        number;

begin

current_term := usf_stu.usf_functions.f_usf_curr_term;
fall_census  := usf_stu.usf_f_as_get_fall_spr_census( substr(current_term,1,4) || 40);
last_fall_census := usf_stu.usf_f_as_get_fall_spr_census( substr(current_term,1,4) - 1 || 40);

-- today <= fall census date + 1    
if trunc(sysdate) <= trunc(fall_census + 1) then
   v_term   := substr(current_term,1,4);
   v_weekno := v_term || lpad(trunc((trunc(sysdate) - trunc(last_fall_census))/7), 2, 0);

   if substr(v_weekno,5) between 0 and 21 then
       v_sem := 20;        -- want 20, 30, 40
   else
       v_sem := 30;        -- want 30, 40
   end if;
end if;

-- today >= fall census date + 1
if trunc(sysdate) >= trunc(fall_census + 1) then
   v_term   := substr(current_term,1,4) + 1;
   v_weekno := v_term || lpad(trunc((trunc(sysdate) - trunc(fall_census))/7), 2, 0);

   if substr(v_weekno,5) between 0 and 21 then
       v_sem := 20;        -- want 20, 30, 40
   else
       v_sem := 30;        -- want 30, 40
   end if;
end if;

--10-DEC-2009


insert into usf_stu.hist_adm_stats

What I am looking for is how to create the loop statement I mentioned in the previous response using Oracle Syntax so that on the first loop it will run the insert statement using the conditions set forth in the <= date and once it has run through that loop, if the loop counter is equal to 2, then it will run through the insert statement again using the conditions set forth in the >= date condition, otherwise if the counter is equal to 1, it will not run through the insert statements a second time.

Pseudo Code:

--Check to see if the sysdate is = to fall_census
--If it is less than fall_census we only want to do the insert statement one time
--If the sysdate is = to fall_census, we want to do the insert statement two times

if trunc(sysdate) <= trunc(fall_census + 1) and trunc(sysdate) >= trunc(fall_census + 1) then
   LOOPCount = 2
Else
   LOOPCount = 1
End

--The conditions for the insert statement this will run regardless of whether the sysdate is less than or equal to the census_date

if trunc(sysdate) <= trunc(fall_census + 1) then
   v_term   := substr(current_term,1,4);
   v_weekno := v_term || lpad(trunc((trunc(sysdate) - trunc(last_fall_census))/7), 2, 0);

   if substr(v_weekno,5) between 0 and 21 then
       v_sem := 20;        -- want 20, 30, 40
   else
       v_sem := 30;        -- want 30, 40
   end if;

--Set up the loop to run 2 times at most

For I = 1 to LOOPCount

---------------------------
--This section will only run when I = 2

If I = 2 Then
-- today >= fall census date + 1
--if trunc(sysdate) >= trunc(fall_census + 1) then
   v_term   := substr(current_term,1,4) + 1;
   v_weekno := v_term || lpad(trunc((trunc(sysdate) - trunc(fall_census))/7), 2, 0);

   if substr(v_weekno,5) between 0 and 21 then
       v_sem := 20;        -- want 20, 30, 40
   else
       v_sem := 30;        -- want 30, 40
   end if;
End If;

------------------------------------------------

insert into histstats

Select a.*  
FROM
(....
 ....
WHERE substr(term,1,4) =  v_term
AND   substr(term,5,2) >= v_sem
 ...
 ...
)  a
;
Commit;

--Now that the insert has taken place, increase the value
--of I and if it is = 2 then do this loop over again if
--LoopCount = 2 and I = 2.  In that case the values --assigned to the parameters where 
--I = 2 will be used.

Next I

 

[sxschech]

Hi Carp and Dagon, I didn't receive the post while I was writing my last response. So with my limited understanding of Carp's code, the Procedure LOAD_TABLE section won't be run when looking at it from a top down approach but will be called and run only when it gets to the statement LOAD_TABLE(v_term, v_weekno)?

 

[Dagon]

I'm a bit lost as to what exactly you do want. Earlier you stated you wanted only a single insert statement, now you seem to be saying you want multiple insert statements.

I'm not entirely sure I understand your last question - obviously the procedure will only be called if it actually gets to that line of the code i.e. if it gets past the IF statements. So Carp's example should work (except that there should be semi-colons after the LOAD_TABLE(v_term, v_weekno) statements).





Retired (not by choice) Oracle contractor.

 

[carp]

Dagon -
Good catch! I have been tripped up (yet again) by the perils of cut/paste.

 

[sxschech]

Dagon and Carp,

I will try the code. Sorry if my explanation in trying to understand what Carp's code is doing is confussion matters. I was confirming, since I don't know too much about Oracle Syntax that the portion where the insert statement is located is essentially like a definition and will only be called to run when the code gets to the line that says LOAD_TABLE(v_term, v_weekno). That is to say that if the program runs line 1, 2 ,3 etc. it will not be running the Insert statement when it sees it in the "Procedure" section, but will wait to run it when it gets to the "If" section. That being the case, I think it will do what I'm after and I will try it out.


Dagon, I'm sorry if my explanation of what I'm after has been confusing...
My goal is to have one occurrence of the insert statement, but be able to via "code" have it run a second time using the second set of parameters when the date condition is true. Disregarding the actual parameters and insert statements, in it's simplest using a plain SQL statement:

Let's Say today is 10-MAR-2010
AND CENSUS_FALL = 12-MAR-2010
The value of VTerm up through 12-Mar would be 201020
The value of VTerm starting with 12-Mar and forward would be 201120
----------------------------------------
If sysdate <= Census_Fall then

SELECT * FROM XYZ WHERE TERM = VTerm --'201020'

If sysdate => Census_Fall then

SELECT * FROM XYZ WHERE TERM = VTerm --'201120'

----------------------------------------

On the 10th and 11th of Mar the Select Statement would run the first SELECT statement "TERM = '201020'"

Ok, so, on the 12th of March, I want to have it run both SQL
statements, since they both are true, but since the statements are identical, with the exception of the WHERE clause, I would like to consolidate this into a Loop so that it can run both queries one after the other on the date where both conditions are true.


Thank you for your assistance and patience with setting this up.

 

[carp]

Yes - you can think of the embedded procedure as being local to the enclosing procedure, much like a local variable. The syntax will be checked, but the inner procedure will only be executed when invoked from the enclosing procedure.

 

[sxschech]

Hi Carp and Dagon,

I tried incorporating the code from Carp's 8-Mar-2010 post and must have missed something as it generates an error on this line:

[tt]
PROCEDURE load_table(p_term IN VARCHAR2, p_weekno IN VARCHAR2) IS v_sem INTEGER;

ERROR line 43, col 4, ending_line 43, ending_col 12, Found 'PROCEDURE', CASE is expected
[/tt]

Anyway, don't worry about it. The good news is I finally found the syntax I needed in order to do the loop as I originally laid out. Turns out all I needed were semicolons at the end of each statement in order to do the ifs and double dots for the loop range and semicolon equal to assign the start values. Also, turns out I didn't need to explicitly increment the value of i at the end of the loop. In my test it seemed to work. Hopefully, it will work in production on that "magic" day.

Here is a snippet:

i := 1;
if trunc(sysdate) = trunc(fall_census + 1) then 
   loopcount := 2;                              
else
   loopcount := 1;
end if;

-- today <= fall census date + 1    
if trunc(sysdate) <= trunc(fall_census + 1) then
   v_term   := substr(current_term,1,4);
   v_weekno := v_term || lpad(trunc((trunc(sysdate) - trunc(last_fall_census))/7), 2, 0);

   if substr(v_weekno,5) between 0 and 21 then
       v_sem := 20;        -- want 20, 30, 40
   else
       v_sem := 30;        -- want 30, 40
   end if;
end if;


FOR i IN 1 .. loopcount loop

if i = 2 then

--do stuff here

end if;

--Select Statement
;

commit;

END LOOP;

end;
/

 

[Dagon]

I think I'd need to see the whole thing. The problem is probably with the placement of your procedure declaration. Are you using a stored procedure or package ?

For Oracle-related work, contact me through Linked-In.

 

[Dagon]

Re-reading your message, it looks as if there isn't a problem, so ignore what I said.

For Oracle-related work, contact me through Linked-In.