SQL Script that only fires every bi-weekly payday 2

[TJIT]

Few years ago someone created a script to set the condition where an email only gets sent on a Thursday of the week we get paid (which is every other week). It's been working for a few years but suddenly stopped in January. I'm guessing it has something to do with it being a leap year, but this sql is way over my head to know for sure. Can someone look at it and tell me if it's set up correctly? Today, Thursday 2-4-16, it was supposed to fire, but did not. I'm sure it's a matter of changing one number but, by the time I figure it out (trial and error guessing), it will be next year.

select decode(nvl((select trim(to_char(sysdate,'DAY'))

FROM dual

where mod(to_char(sysdate,'IW'),2)=0

and

(trim(to_char(sysdate,'DAY'))='THURSDAY' or
trim(to_char(sysdate,'DAY'))='THURSDAY')),0), 'THURSDAY',1,0)from dual

 

[johnherman]

The logic should work, leap year or not. Have you checked to see if the object is valid? For instance, if this is a stored procedure, it may have become "invalid" by some internal Oracle process, and Oracle upgrade or patch, etc. You can recompile the object and try again.

==================================
adaptive uber info galaxies (bigger, better, faster, and more adept than cognitive innovative agile big data clouds)

 

[TJIT]

This is used in an automated emailing software as a "condition statement". It tells the software to only run and email a particular report every other week. It was working consistently right up until the first payroll of January. Nothing in the report or any other area of the software changed. That's why I thought it just needed tweaked because of something different about 2016, leap year being the only obvious difference.

 

[Andrzejek]

I would guess your Stored Procedure runs every night, and when it detects alternate Thursday it sends some e-mails. And it does nothing all other days.
I would first check if this procedure runs at all, and if it does - check which piece of code should work but does not.

If you don't want this job to run every night but only every other Thursday, you may want to investigate Oracle Scheduler Then you will not need any piece of code detecting the alternate Thursdays.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[TJIT]

If I run the procedure manually, it runs just fine. It's been running great for a few years. It was only when it needed to run the first time in January that it stopped working. That's why I think it's calendar related.

The software this runs in is called IQALERT and is part of our companies' off the shelf ERP software so I have limited flexibility to do anything but run a conditional statement.

 

[Beilstwh]

If you are running it as a dba_job check the queue to see what is the next scheduled date. If it returns 0 does it mean don't send the email and 1 means send it? Run the following query to see if the job is still scheduled.

SELECT JOB,last_date,next_date,INTERVAL
FROM user_jobs
WHERE instr(upper(what),'NAME_OF_THE _PROCEDURE_IN_UPPERCASE') > 0;


I would suggest that you use dbms_schedule like Andy suggested. You can take out your test in the code and just tell the scheduler to run every 2 weeks on a thursday.

Bill
Lead Application Developer
New York State, USA

 

[taupirho]

I'm assuming this should return 1 when you're expecting it to fire. So, the IW bit gets the week number of the year which for the 4th Feb returns 05 on my system. So the mod.... part returns 1 therefore the thing you selecting returns 0 and not 1.

In order to understand recursion, you must first understand recursion.

 

[Beilstwh]

Instr returns the position of the string within the what column. it returns 0 if it can't find the string, it returns the position in what if it does.

Bill
Lead Application Developer
New York State, USA

 

[TJIT]

Last Thursday I systematically changed the two numbers at the end of this part of the script using combinations of 0's, 1's, and 2's:

where mod(to_char(sysdate,'IW'),2)=0

I found that ,2)=1 resulted in no action being performed (in my case an email didn't get sent out)
I also found that ,2)=0 resulted in an action

Monday (new week) I then tried the same combination of numbers and the opposite actions happened. I believe this is my answer. Best I can tell is that 'IW' looks at the (Julian?) number in the year and divides it by the number after the equal sign. The resulting value is Boolean. Best I can figure out in my limited experience. I have it set to auto-email this Wed. Will report back the results.

 

[Beilstwh]

The easiest way to only schedule every other week on a Thursday at 3am (for example) is to build a function. I have included the code for a function and gave an example of building the batch job. Adjust the code to your own requirements

CREATE OR REPLACE FUNCTION Run_date
RETURN DATE
IS
V_date DATE;
BEGIN
SELECT My_date
INTO V_date
FROM (SELECT TRUNC (SYSDATE + ROWNUM) My_date
FROM All_objects
WHERE ROWNUM < 15) X
WHERE MOD (TO_NUMBER (TO_CHAR (X.My_date, 'ww')), 2) = 1
AND X.My_date > TRUNC (SYSDATE)
AND TO_CHAR (X.My_date, 'DY') = 'THU';

-- Fire the next job at 3:30 am
V_date := V_date + (210 / 1440);
RETURN V_date;
END Run_date;
/



DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'BEGIN
your_program;
END;'
,next_date => to_date('18/02/2016 03:00:00','dd/mm/yyyy hh24:mi:ss')
,interval => 'RUN_DATE'
,no_parse => FALSE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;
/


Bill
Lead Application Developer
New York State, USA

 

[karluk]

TJIT, as your testing shows, starting in 2016 your email should run on odd numbered weeks whereas in 2015 it needed to run on even numbered weeks. The reason for this difference appears to be the fact that a 365 day year contains one day more than a full 52 weeks.

Consider the situation on December 31, 2015, which was the last day of 2015 and also a Thursday. It looks as if the 'iw' date format treated December 31 as being in the 53rd week of 2015, and your script (correctly) did not send an email, because it wasn't the week you got paid.

Now fast forward to January 7, 2016 - the following Thursday. The 'iw' format calculates this to be in week one of 2016 and, because the logic in your script sends emails only on even numbered weeks, you get no email. From your point of view, it's the week you got paid and you are expecting an email, but 'iw' has calculated two consecutive weeks with the odd numbers of 53 and 1, so no email is forthcoming.

So, for the purpose of getting your script to run in 2016, you should just replace '0' with '1' in your script. However, this is not a long term fix. Sooner or later the same situation will arise with Thursday in week 53 followed by Thursday in week 1. When that happens, your script will stop running again, and you will have to change the '1' back to '0'. It would be better to take the advice of some of the other posters and run this job using scheduler software. Then you won't run into these little glitches.