Suggestion in Autosys job

UNIX for Beginners Q & A
1

Hi,

I am new to Unix and Autosys. I have written Oracle query. Now I would like to create Autosys job. below is the requirement.

  1. Send in email every 30 minutes from 11 to 4:00 PM. E-mail should only be sent ONLY when below Query have data return.
  2. Email format:
    [list]
  3. To: ,
  4. From: ,
  5. Email Subject: Data List
  6. Email content:
    [/list]
select ts.sup_num, ts.s_add_num, 

ts.ind_qty, ts.total_qty, tb.b_qty, tt.tri_qty ,
case when ts.total_qty-tb.b_qty > tt.tri_qty then tt.tri_qty
else ts.total_qty-tb.b_qty end as rls_qty
from
(
select sup_num, s_add_num, count(*) , abs(sum(sh_qty)) as total_qty , LISTAGG(sh_qty, ',') WITHIN GROUP (ORDER BY updt_last_tmstp ) as ind_qty
from p_sls_trn where trunc(trans_dt) = '16-aug-2017' and dc_cd='C' and brc_cd='002'
group by s_add_num ,sup_num
) pts,
( select s_add_num, abs(settlement_dt_qty) as b_qty from tash where brc_cd||act_cd||TYPE_act_cd||act_nbr = '120000000987'
) tsh_box,
( select s_add_num, abs(settlement_dt_qty) as tri_qty from tash where brc_cd||act_cd||TYPE_act_cd||act_nbr in ('120000000087','120000009874')
)tsh_tri
where ts.s_add_num = tb.s_add_num and ts.s_add_num = tt.s_add_num
and ts.total_qty > tb.b_qty and tt.tri_qty > 0 ;

Can you please advise to create a job.

Thanks,
Vasu

2

Assuming you know how to create job in autosys.

Step1 : Create a script which will execute the query and populate it to a file. If the file is having more that one record you can execute a mailx to send the email

Step2: Create a job and make that to run every 30 min using the autosys start_time parameter

Let me know if you need more details