Home » SQL & PL/SQL » SQL & PL/SQL » Report not formating columns properly (Oracle 10G)
Report not formating columns properly [message #427038] Tue, 20 October 2009 13:46 Go to next message
SUTTONM
Messages: 3
Registered: October 2009
Location: USA
Junior Member
Below is the SQL code I want to execute, but as you can see, I am trying to limit the number of characters in the columns, with no success. In example, the employee column still shows 30 characters in the output file:

rem oup: 6-oct-98. expanded size of wght_book_avg field from 10
rem oup: 25-apr-00 added +1 to end_time so no division by zero
set termout off
drop table moverate;
set termout on
create table moverate
(emp_name varchar2(20),
task_type varchar2(10),
no_picks number(9),
no_eaches number(9),
pick_rate number(12,2),
book_rate number(12,2),
wghted_avg number(9),
wght_book_avg number(14),
start_time number(4,2),
end_time number(4,2));
column today new_value xtoday noprint
column time new_value xtime noprint
column aa heading 'No Mov' format 999999
column bb heading 'No Books' format 999999999
column cc heading 'Moves per Hour' format 9999999.99
column dd heading 'Wghted Pick Avg.' format 999999.99
column ee heading 'Books per Hour' format 9999999.99
column ff heading 'Wghted Book Avg.' format 9999.99
column gg heading 'Employee' format a10
column hh heading 'Type' format a4
column ii heading 'Start' format 999.90
column jj heading 'End' format 999.90
break on report skip 1
compute sum of aa on report
compute sum of bb on report
select to_char(sysdate,'DD-MON-YY') today from dual;
select to_char(sysdate,'HH24:MI:SS') time from dual;
insert into moverate
select e.emp_name, p.task_type,count(*), sum(qty) ,
(count(*)/(max(end_time)+1-min(begin_time))) *3600 ,
(sum(qty)/(max(end_time)+1-min(begin_time))) *3600 ,
count(*)*((count(*)/(max(end_time)+1-min(begin_time)))*3600),
sum(qty)*((sum(qty)/(max(end_time)+1-min(begin_time)))*3600),
min(begin_time)/3600,(max(end_time)+1)/3600
from emp e, pltmovsg p
where e.emp_no=p.assg_emp_id
and p.begin_time is not null
and p.task_type in ('PUT','MOV')
and trunc(p.date_time_Created)=trunc(sysdate)
group by e.emp_name,p.task_type
/
set pages 135
set verify off
set feedback off
set echo off
ttitle left 'Moves and Puts Rates for ' xtoday ' as of ' xtime skip 2
spool c:\marc\output\moverate.lis
select substr(emp_name,1,10) gg,substr(task_type,1,3) hh,
substr(no_picks,1,4) aa,
no_eaches bb,
substr(start_time,1,5) ii,substr(end_time,1,5) jj
from moverate
order by emp_name
/
drop table moverate;
spool off

host c:\program files\windows nt\accessories\wordpad c:\marc\output\moverate.lis
--host lpr -S cary-whse -P lasr18 -o l -d c:\marc\output\moverate.lis

exit
Re: Report not formating columns properly [message #427042 is a reply to message #427038] Tue, 20 October 2009 14:04 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
SUTTONM wrote on Tue, 20 October 2009 13:48

I am trying to limit the number of characters in the columns, with no success


SQL> COLUMN column_name FORMAT A10
Re: Report not formating columns properly [message #427046 is a reply to message #427042] Tue, 20 October 2009 14:34 Go to previous messageGo to next message
SUTTONM
Messages: 3
Registered: October 2009
Location: USA
Junior Member
Its_me_ved wrote on Tue, 20 October 2009 14:04
SUTTONM wrote on Tue, 20 October 2009 13:48

I am trying to limit the number of characters in the columns, with no success


SQL> COLUMN column_name FORMAT A10

Thank you! That worked for that specific statement, however the same applies to other statements but when I change it to A10, the "dollar" value that would normaly be displayed turns to all ##########'s.
Re: Report not formating columns properly [message #427047 is a reply to message #427046] Tue, 20 October 2009 14:38 Go to previous messageGo to next message
SUTTONM
Messages: 3
Registered: October 2009
Location: USA
Junior Member
Columns II and JJ are doing the same I meant to say, so changing that to A10 will break the dollar values.
Re: Report not formating columns properly [message #427048 is a reply to message #427047] Tue, 20 October 2009 14:58 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Quote:

Columns II and JJ are doing the same I meant to say, so changing that to A10 will break the dollar values.


Did not understand what you meant.




My previous answe was for the statement you wrote
Quote:

I am trying to limit the number of characters in the columns, with no success. In example, the employee column still shows 30 characters in the output file:


Re: Report not formating columns properly [message #427049 is a reply to message #427047] Tue, 20 October 2009 15:03 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
> when I change it to A10, the "dollar" value that would normaly be displayed turns to all ##########'s.

####### are displayed when value is wider than specified format.

>Columns II and JJ are doing the same I meant to say, so changing that to A10 will break the dollar values.
Oracle does not know anything about dollars.
When I break a dollar, I get 1 or more coins in return.


You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

in the future, please use CUT & PASTE

Re: Report not formating columns properly [message #427082 is a reply to message #427038] Wed, 21 October 2009 00:43 Go to previous message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL*PlusĀ® User's Guide and Reference
Chapter 6 Formatting SQL*Plus Reports

Regards
Michel
Previous Topic: how to know no of users in sql prompt
Next Topic: PROBLEM WRITING QUERY
Goto Forum:
  


Current Time: Wed Dec 07 03:24:10 CST 2016

Total time taken to generate the page: 0.10596 seconds