CODEWITHSUNDEEP
oracle-databasereportsqlplusspool
Syah
Syah

Reputation: 463

SQL*Plus Custom Summary Report

I have a requirement to generate the following summary report that look like the following:

enter image description here

My problem is that,

  1. I have no idea on how do I fill the count data in this custom report.
  2. I do not know how to put it in a table view like the above in a text document. It is not HTML.

So far, I only know how to do the first row, and column without the table view.

Here are my codes.

SET HEADING OFF;
SET LINESIZE 200;
SET SPACE 0; 
SET ECHO OFF;
SET FEEDBACK OFF;
SET VERIFY OFF;
SET MARKUP HTML OFF SPOOL OFF;
SET TERMOUT OFF; --Do not show output to the screen.
SET NEWPAGE NONE; --Remove the first blank line at the top of the page and between queries.

TTITLE LEFT "REPORT NAME" RIGHT "PAGE : " SQL.PNO SKIP 1 -
LEFT        "--------------------------------" RIGHT "DATE : " _DATE SKIP 1 -
LEFT "A) TOTAL RECORDS " RIGHT total_records; -- Cannot output variable in the title.
LEFT "B) MATCHED RECORDS " RIGHT matched_records; -- Cannot output variable in the title.
LEFT "C) UNMATCHED RECORDS " RIGHT matched_records; -- Cannot output variable in the title.
BTITLE LEFT "E N D";

total_records is an insert into statement.

SELECT COUNT(*) INTO total_records FROM TABLE;

I have not done matched records and unmatched records. But the only way I can think of

  1. Select a statement into a cursor.
  2. Loop into the cursor.
  3. Increase matched count when there is a match.
  4. Once loop finish. unmatched count = total count - matched count.

I don't think this is the most efficient way. But, if you have a better way, let me know.

Upvotes: 0

Views: 196

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

Does something like this ring a bell? Example is based on Scott's sample schema:

SQL> select 'Total records' name, count(*) cnt
  2    from emp
  3  union all
  4  select 'Matched count', sum(case when deptno = 10 then 1 else 0 end)
  5    from emp
  6  union all
  7  select 'Unmatched count', sum(case when deptno = 10 then 0 else 1 end)
  8    from emp;

NAME                   CNT
--------------- ----------
Total records           14
Matched count            3
Unmatched count         11

SQL>

Upvotes: 1

Related Questions