I'm trying to run a second report based on the first report that must have at least one record. I keep getting a blank page as output, I know the first report has records
Any ideas?? Thanks for any and all help


ENGINE SQLORA SET DEFAULT_CONNECTION prodmfg
SQL SQLORA PREPARE SQLOUT FOR
select th.reshot_id,
th.reshot_count as total_rs_cnt,
th.reshot_limit,
th.reshot_desc,
NVL(c.rd_emp_no, '0000') || '-' || NVL(rde.f_name, 'NOREADER') as reader_emp,
NVL(c.ch_emp_no, '0000') || '-' || NVL(che.f_name, 'NOEMPCHARGED') as charged_emp,
count(c.reshot_id) as emp_rs_cnt,
mr.file_no as film_file_no,
mr.workorderno
from xray.charge c,
xray.list_reshot rl,
xray.employee rde,
xray.employee che,
xray.master_record mr,
(select reshot_id,
reshot_count,
basic_threshold as reshot_limit,
reshot_Desc
from (select rs.reshot_id,
count(rs.reshot_id) as reshot_count,
lr.basic_threshold,
lr.reshot_desc
from xray.charge rs, xray.list_reshot lr
where lr.plant_number = '1'
and rs.reshot_id = lr.reshot_id
and rs.rd_emp_dept_code in ('648', '146')
and rs.charge_date >= sysdate - 10/24
group by rs.reshot_id, lr.basic_threshold, lr.reshot_desc
)
where reshot_count > basic_threshold
and basic_threshold <> 0) th
where c.reshot_id = th.reshot_id
and rl.plant_number = '1'
and c.rd_emp_no = rde.employeeno(+)
and c.ch_emp_no = che.employeeno(+)
and c.reshot_id = rl.reshot_id
and c.rd_emp_dept_code in ('648', '146')
and c.file_no = mr.file_no
and c.charge_date >= sysdate - 10/24
group by th.reshot_id,
NVL(c.rd_emp_no, '0000') || '-' || NVL(rde.f_name, 'NOREADER'),
NVL(c.ch_emp_no, '0000') || '-' || NVL(che.f_name, 'NOEMPCHARGED'),
mr.file_no,
mr.workorderno,
th.reshot_count,
th.reshot_limit,
th.reshot_desc;
END
TABLE FILE SQLOUT
PRINT
CNT.RESHOT_ID NOPRINT
END
-RUN
IF CNT.RESHOT_ID NE 0 GOTO SECRPT;
-EXIT
-SECRPT
SQL SQLORA PREPARE SQLOUT FOR
select th.reshot_id,
th.reshot_count as total_rs_cnt,
th.reshot_limit,
th.reshot_desc,
NVL(c.rd_emp_no, '0000') || '-' || NVL(rde.f_name, 'NOREADER') as reader_emp,
NVL(c.ch_emp_no, '0000') || '-' || NVL(che.f_name, 'NOEMPCHARGED') as charged_emp,
count(c.reshot_id) as emp_rs_cnt,
mr.file_no as film_file_no,
mr.workorderno
from xray.charge c,
xray.list_reshot rl,
xray.employee rde,
xray.employee che,
xray.master_record mr,

(select reshot_id,
reshot_count,
basic_threshold as reshot_limit,
reshot_Desc
from (select rs.reshot_id,
count(rs.reshot_id) as reshot_count,
lr.basic_threshold,
lr.reshot_desc
from xray.charge rs, xray.list_reshot lr
where lr.plant_number = '1'
and rs.reshot_id = lr.reshot_id
and rs.rd_emp_dept_code in ('648', '146')
and trunc(rs.charge_date) >= sysdate - 1
group by rs.reshot_id, lr.basic_threshold, lr.reshot_desc)
where reshot_count > basic_threshold
and basic_threshold <> 0) th
where c.reshot_id = th.reshot_id
and rl.plant_number = '1'
and c.rd_emp_no = rde.employeeno(+)
and c.ch_emp_no = che.employeeno(+)
and c.reshot_id = rl.reshot_id
and c.rd_emp_dept_code in ('648', '146')
and c.file_no = mr.file_no
and trunc(c.charge_date) >= sysdate - 1
group by th.reshot_id,
NVL(c.rd_emp_no, '0000') || '-' || NVL(rde.f_name, 'NOREADER'),
NVL(c.ch_emp_no, '0000') || '-' || NVL(che.f_name, 'NOEMPCHARGED'),
mr.file_no,
mr.workorderno,
th.reshot_count,
th.reshot_limit,
th.reshot_desc;
END
TABLE FILE SQLOUT
PRINT
RESHOT_ID
TOTAL_RS_CNT/D10
RESHOT_LIMIT/D10
RESHOT_DESC
READER_EMP
CHARGED_EMP
EMP_RS_CNT/D10
FILM_FILE_NO
WORKORDERNO
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
END

This message has been edited. Last edited by: Kerry,

The right way to check for no records in a report is to USe &LINES.

-RUN
-IF &LINES EQ 0 GOTO SECRPT;
-EXIT

Thanks, I changed it as suggested and I get the same result..nothing

HAve yupe set &ECHO=ALL, to see what value &LINES is ?

Can you post the code output ?

Since your IF statement is a Dialogue Manager statement, it requires a dash in front:

-IF &LINES NE 0 GOTO SECRPT;

TABLE FILE SQLOUT
PRINT
CNT.RESHOT_ID NOPRINT
END
-RUN
IF CNT.RESHOT_ID NE 0 GOTO SECRPT;
-EXIT
-SECRPT

should be:

TABLE FILE SQLOUT
PRINT
CNT.RESHOT_ID NOPRINT
ON TABLE HOLD FORMAT ALPHA
END
-RUN
-IF &LINES NE 0 THEN GOTO SECRPT;
-EXIT
-SECRPT

Thanks..I now get a report, the report comes out in html format even though I specified excel and has page1, page2,page3 then data any ideas?

I find it safer to use:

 
....

TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS INTERIM
END
TABLE FILE INTERIM
....
ON TABLE PCHOLD FORMAT EXL2K
END

I works better as a general rule to keep the TABLE FILE SQLOUT portion as generic as possible.