Output to a file using a SELECT query 1

[pnad]

Hello,

I have a SELECT query(pasted below) that I use to output a file. The users now want the total number of checks and sum of checs outputted at the end of the file with labels. I thought dbms_output.put_line would work but it gives me an error. Please help !

SET FEEDBACK off
SET HEADING off
SET TERM off
SET VERIFY off
SET ECHO OFF
SET TRIMSPOOL OFF

set linesize 140
col col2 format 140
col col3 format 140
col col1 format 140

SPOOL C:\output.txt

select '01' || '000' || '1234512345' ||
rpad(substr(a.check_number, 2, 10) || ' ', 10) ||
rpad(to_char(a.check_date, 'yyyymmdd') || ' ', 8)||
rpad(lpad(trunc(a.check_amt * 100, 0), 11, '0') || ' ', 11)||
rpad(' ',15) ||
decode(a.check_recon_status, 'V', 'V',' ')||
rpad(' ',80) as col1
from table a, table b
where a.tran_id = b.tran_id
Union
select '01' || '000' || '1234512345' ||
lpad(count(a.check_number),10,0)||
rpad(to_char(a.check_date, 'yyyymmdd') || ' ', 8) ||
rpad(lpad(trunc(sum(a.check_amt) * 100, 0), 11, '0') || ' ', 107) as col2
from table a, table b
where a.tran_id = b.tran_id
group by a.check_date
Union
select '01' || '000' || '1234512345' ||
lpad(count(a.check_number),10,0)||
rpad(to_char(a.check_date, 'yyyymmdd') || ' ', 8) ||
rpad(lpad(trunc(sum(a.check_amt) * 100, 0), 11, '0') || ' ', 107) as col3
from table a, table b
where a.tran_id = b.tran_id
group by a.check_date;

dbms_output.put_line('Number of Checks = '||lpad(count(a.check_number),10,0);
SPOOL off

SET PAGESIZE 14
SET TERM on
SET VERIFY on
SET FEEDBACK on
SET HEADING on

exit

Thanks.

 

[KenCunningham]

What is the error?

I want to be good, is that not enough?

 

[Dagon]

I'm afraid dbms_output.put_line only works in PL/SQL. Have you look at the grouping functionality in SQL*Plus ?

 

[hoinz]

Compute command might be what you are looking for,
have a look at:

http://download.oracle.com/docs/cd/A91202_01/901_doc/win.901/a88826/ch812.htm#286262

Put something like this at the beginning of your script:
break on report
compute count of check_number on report
compute sum of check_amt on report

It has been a while since I last did something like that, so you will have to work out the details by yourself.

 

[Dagon]

More specifically, the COMPUTE command i.e. this sort of thing:

BREAK ON JOB_ID SKIP 1;
COMPUTE SUM LABEL 'TOTAL' OF SALARY ON JOB_ID;
SELECT JOB_ID, LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE JOB_ID IN ('AC_MGR', 'SA_MAN')
ORDER BY JOB_ID, SALARY;

 

[pnad]

I added these 2 lines:

BREAK ON a.check_number SKIP 1
COMPUTE COUNT LABEL count OF a.check_number ON a.check_date

before the SELECT clause. The SQL ran without any errors but it did not print the label out.

 

[hoinz]

Have a closer look at the link I gave above; e.g. you will see this:

(column cannot have a table or view appended to it. To achieve this, you can alias the column in the SQL statement.)

And feel free to play aroud a bit!

 

[ChrisHunt]

I'd just add another query to the end:

SELECT 'Number of Checks = '||COUNT(*),
       'Sum of Checks = '||SUM(a.check_amt)
FROM   table a, table b
WHERE  a.tran_id = b.tran_id;

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[Dagon]

Not the most efficient thing to do, since that means you would be querying the tables twice.