SQL help

[AlStl]

I am in a odd situation. Following is my query running against the dataset as show below:

Select name from table_name
where (send_yn='Y' or send_yn='N') and (last_change_dt = sysdate -1)
and (last_change_dt > sent_system_dt OR sent_system_dt is null)

Users via front end manipulates send_yn (by marking it Y and N) and in doing so last_change_dt also gets updated with current time and date. This SQL is wrapped within a unix shell script. Once a user marks a record Y or N all records belonging this person with flag Y (name) will be extracted via this SQL and sent to another system. So the trigger is marking Y or N. Once the records are sent sent_system_dt gets update with current time and stamp. My SQL will only work for the records that are marked a day prior (last_change_dt = date -1).

Shell script runs via cron every morning to process records marked by the user a day prior. For some reason if this unix script fails to run for a day...i will be in a jam because during next run (following day) date-1 will not yield any records since date-1 will not return anything. Basically at that time it should be date-2. How to fix this SQL to take care of issues that arise if unix shell script fails to run for certain day or two.

Name send_yn last_change_dt sent_system_dt
joe smith N 2012-03-06 10:35:33 2011-10-19 05:00:15
joe smith N 2012-03-06 10:21:11 2011-10-19 05:00:46
joe smith Y 2011-10-18 14:03:55 2011-10-19 05:00:15
Tom smith N 2012-03-05 10:35:33 2011-10-19 05:00:00

Based on above data and running my SQL today i.e. 03/07/12 i get:

Joe Smith

But what is Unix script failed to run this morning ...how can a make sure that Joe Smith gets picked up during tomorrow run i.e. 3/8/12 ?

Thanks,

Al

 

[SantaMufasa]

Al,

I have a couple of questions about your code, below:

Select name from table_name
 where (send_yn='Y' or send_yn='N')
   and (last_change_dt = sysdate -1)
   and (last_change_dt > sent_system_dt OR sent_system_dt is null)

[ul][li]What value could SEND_YN be besides 'Y' or 'N'? (NULL perhaps?) If it cannot be a value besides 'Y' or 'N', then why are you checking at all?[/li][li]Why do you want to process "just yesterday's" changes?[/li][li]If you are not using the TRUNC() function on both LAST_CHANGE_DATE and SYSDATE, you are probably going to miss many changes that happened yesterday, but earlier than this time yesterday, right?[/li][li]Aren't you really interested simply processing these changes:

...WHERE send_yn = 'Y'
     AND sent_system_dt IS NULL

? If this is not the case, please correct my misunderstanding.[/li][/ul]

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[AlStl]

Mufasa,

How you doing?

Let me try and explain the scenario and that will answer your questions as well.

Unix job that has embeded SQL runs at 5:00 am every morning. The goal is to process all records marked by the users a day prior.
Now, users expect to only send all records that are marked 'Y', but they do not necessarly mark the records 'Y' that they intend to send. What they can do is: Mark a record for Jeo Smith as 'N' that will also update the field last_change_dt with current time stamp. So, in other words marking a record 'Y' and 'N' is the trigger to scan the table for all records with 'Y' and send them to another system via FTP.

Once records are send successfully sent: script updates sent_system_dt with current time stamp where send_yn='Y' and name='name'

My problem is this will work just fine as long as script runs daily at 5:00 am (hence sysdata -1)

If script fails to run and no one notices it for say 2 days...then this logic will not work. I should say it will not work entirely and will only send records that are modified a day prior.

Al

 

[carp]

This still leaves Santa's first question unanswered. Can send_yn have any values besides 'Y' or 'N'? If not, then you would do yourself a favor by losing that part of your filter.

As far as the script failure scenario goes, what comes to mind is a table that would hold the date that the job last ran. After running the query, the script could update this value. Then you could join this table in your query to set up your threshold date.

 

[AlStl]

Sorry, possible values in Send_yn are 'Y' or 'N' or NULL.

I do not have the luxury to make changes to table structure. It would make my life lot simpler if that was the case.

 

[majlumbo]

where Send_yn is Not NULL

would be better than checking for 'Y' or 'N'

 

[AlStl]

I think you guys make a good point to get rid off checking 'Y' or 'N', but i am still searching for an answer to the question i.e. how to add logic to handle for skip days if unix shell script does not run?

 

[majlumbo]

If you can't make changes to the database structure, then use a file available to your Unix Script that is updated with the last successful run date of the script, then alter your SQL to use the date provided from the file, rather than sysdate - 1.

 

[carp]

I agree with majlumbo. The bottom line is that you need to record your "last run time" someplace. If you cannot create a new table in the database or a column somewhere you can store the date in, then you will need to use a file someplace.

Have you discussed this with the DBA? If this is an absolute business requirement, the DBA should be able to provide a table or column for you. If the DBA refuses to do so, then I would explain your situation to the business and let them decide whether they want to push the DBA or accept the possibility of a gap in their reports.

 

[majlumbo]

I'd also have to agree with carp and would rather a database solution. Using a file to hold the date has some obvious problems. You'd have to handle if the file was missing, or if its 'structure' get altered, or even where a (partial) file system restore overwrites a file with an earlier version of the file.