0

I have a table with a date column that reflects the date when some job started, and all entries related to the same job should have the same date. The job can last until the next day, but I want the date to be the same for all entries related to the same job.

For the next run of the job, again all entries should have the date when the job started.

I tried with default date as sysdate:

alter table myTable modify mydate default trunc(sysdate)

However, that gives different dates for jobs that run over the next day.

Any body has any suggestions of how to achieve this?

Pablo
  • 55
  • 8
  • Wouldn't it be more useful to record the actual start of each job entry, as you are now doing by accident; and just get the parent job start time when you specifically need to know that? – Alex Poole Jan 21 '16 at 17:17
  • Thanks Alex. The table contains almost identical data for different jobs, only the date is different and I want to easily group together rows from same job – Pablo Jan 21 '16 at 17:24
  • So you can only identify that an entry belongs to a particular job from its start time? There's no ID or run number or anything that's shared among the sub-jobs? Can you even identify the parent job - are the entries, say, numbered as sub-jobs? – Alex Poole Jan 21 '16 at 17:26
  • The plan was to have the date as the ID – Pablo Jan 21 '16 at 17:27
  • 1
    I'd prefer a synthetic key to a natural one personally (well, not quite a key if rows in the same table share it). Either way the parent job will have to record its start time and pass it (or a synthetic key) to the sub jobs, or whatever creates the entries. If all the work is in one session you could use a user context to hide that in the background perhaps. – Alex Poole Jan 21 '16 at 17:30
  • If you want to group rows for the same job, store something that identifies the job. – Dan Bracuk Jan 21 '16 at 17:42
  • As others have mentioned, it would be best to have a separate reference table to store a job_ID, start/end dates, who launched it, other context info, etc. Then you'd simply have a sequence generating IDs for you, and you'd insert this ID into your main tables along with the data. Your reference table would have a single row with the ID and other relevant data for that job. – tbone Jan 21 '16 at 19:39

0 Answers0