Untitled

SET echo off;
SET termout off;
SET head off;
SET feedback off;
SET trimspool ON;
SET linesize 800;
SET pagesize 0;


DECLARE

CURSOR c1 IS
SELECT pupil_number,school,absent_date FROM (
SELECT DISTINCT
sa.pupil_number,
sa.school,
sa.absent_date
FROM sec_attendances sa inner join students s ON s.pupil_number = sa.pupil_number
inner join schools sc ON sc.school = sa.school
WHERE (sa.reason_code IN ('02','26'))
AND TO_DATE(sa.absent_date) = TRUNC(SYSDATE)
AND sa.pupil_number NOT IN (SELECT SUBSTR(pupil_number,2) FROM swcs_global_conn_temp)
AND sa.period = sc.period
AND sa.absent_code <> 'L'
UNION
SELECT DISTINCT
ea.pupil_number,
ea.school,
ea.absent_date
FROM elem_attendances ea inner join students s ON s.pupil_number = ea.pupil_number
WHERE  ea.reason_code_am IN ('02','26')
AND TO_DATE(ea.absent_date) = TRUNC(SYSDATE)
AND s.grade IN ('KA','KP','KG','01','02','03','04','05','06','07','08','09','10','11','12')
AND ea.pupil_number NOT IN (SELECT SUBSTR(pupil_number,2) FROM swcs_global_conn_temp))
ORDER BY school;

spool "someDir\test1.csv";

SELECT 'Pupil_Number, School, Absence_Code, Absence_Date' FROM dual;
BEGIN

FOR rec IN c1
LOOP
DBMS_OUTPUT.put_line('S' || rec.pupil_number || ' ' || rec.school || ' ' || 'U ' || rec.absent_date);
--add insert into temp table
END LOOP;

spool off;