| Author |
Message |
DA Morgan
*nix forums Guru
Joined: 06 Mar 2005
Posts: 1042
|
 Posted: Fri Feb 25, 2005 12:59 am Post subject:
Re: Compute in Oracle
|
|
|
maya wrote:
| Quote: | Frank,
Lets say I have a variable "teacheraverage"
I would say,
BTITLE column xxx teacheraverage
and then for breaks ?
break on ..[ whats the format] here to display the variable under each
teacher's...
|
http://tahiti.oracle.com
We are not hear to do your work for you. Please read the manual.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond) |
|
| Back to top |
|
 |
Frank van Bortel
*nix forums Guru
Joined: 19 Apr 2005
Posts: 804
|
| Posted: Thu Feb 24, 2005 7:31 pm Post subject:
Re: Compute in Oracle
|
|
|
Frank van Bortel wrote:
| Quote: | maya wrote:
Frank,
Lets say I have a variable "teacheraverage"
I would say,
BTITLE column xxx teacheraverage
and then for breaks ?
break on ..[ whats the format] here to display the variable under each
teacher's...
break on (whatever you want the teacheraverage to follow on) skip page
and it *is* all in the SQL*Plus User's Guide and reference |
--
Regards,
Frank van Bortel |
|
| Back to top |
|
|
Frank van Bortel
*nix forums Guru
Joined: 19 Apr 2005
Posts: 804
|
| Posted: Thu Feb 24, 2005 7:30 pm Post subject:
Re: Compute in Oracle
|
|
|
maya wrote:
| Quote: | Frank,
Lets say I have a variable "teacheraverage"
I would say,
BTITLE column xxx teacheraverage
and then for breaks ?
break on ..[ whats the format] here to display the variable under each
teacher's...
break on (whatever you want the teacheraverage to follow on) skip page |
--
Regards,
Frank van Bortel |
|
| Back to top |
|
|
maya
*nix forums beginner
Joined: 16 Feb 2005
Posts: 25
|
| Posted: Thu Feb 24, 2005 5:53 pm Post subject:
Re: Compute in Oracle
|
|
|
Frank,
Lets say I have a variable "teacheraverage"
I would say,
BTITLE column xxx teacheraverage
and then for breaks ?
break on ..[ whats the format] here to display the variable under each
teacher's... |
|
| Back to top |
|
|
maya
*nix forums beginner
Joined: 16 Feb 2005
Posts: 25
|
| Posted: Thu Feb 24, 2005 5:37 pm Post subject:
Re: Compute in Oracle
|
|
|
Frank,
can u give me an example ?
Mahesh Rajendran wrote:
| Quote: | I hate to say this but you are beating a dead horse
wise words.
I would take it. |
|
|
| Back to top |
|
|
Mahesh Rajendran
*nix forums beginner
Joined: 03 May 2005
Posts: 24
|
| Posted: Wed Feb 23, 2005 7:41 pm Post subject:
Re: Compute in Oracle
|
|
|
| Quote: | I hate to say this but you are beating a dead horse
|
wise words.
I would take it. |
|
| Back to top |
|
|
Frank van Bortel
*nix forums Guru
Joined: 19 Apr 2005
Posts: 804
|
| Posted: Wed Feb 23, 2005 7:39 pm Post subject:
Re: Compute in Oracle
|
|
|
maya wrote:
| Quote: | I will post my example one more time
Principal Teacher Student co su Percent
----------- ------------- ------------ --- --- ---
PRIN1 teach1 stud1 7 5 71
PRIN2 teach1 stud2 4 3 75
teach1 total 11 8
teach1 average 72.73
here 72.73 is the average and it is calcuated as: 8*100/11 [ from the
other totals]
Is there a way for me to display this average [ teach1 average
72.73
] in the correct location ?
|
Not the way you want it, but some placement is possible, using
BTITLE COL xx your_variable
Alternatively, TTITLE (Top, vs. Bottom for BTITLE).
Means you would need to introduce a page break on
each compute where you want the variable printed.
Sorry, but SQL*Plus is not a report formatter (used to
have RPT/RPF for that - it's called Oracle Reports now)
--
Regards,
Frank van Bortel |
|
| Back to top |
|
|
DA Morgan
*nix forums Guru
Joined: 06 Mar 2005
Posts: 1042
|
| Posted: Wed Feb 23, 2005 6:58 pm Post subject:
Re: Compute in Oracle
|
|
|
maya wrote:
| Quote: | can you give some examples ?
|
I hate to say this but you are beating a dead horse.
Why not get a real report writer and have the ability
to write decent reports? Might I suggest Oracle
Discoverer, Crystal Reports, Cognos, etc.
Alternatively use the built-in UTL_FILE package to
write a text file and then print it.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond) |
|
| Back to top |
|
|
maya
*nix forums beginner
Joined: 16 Feb 2005
Posts: 25
|
| Posted: Wed Feb 23, 2005 5:27 pm Post subject:
Re: Compute in Oracle
|
|
|
|
can you give some examples ? |
|
| Back to top |
|
|
Mahesh Rajendran
*nix forums beginner
Joined: 03 May 2005
Posts: 24
|
| Posted: Wed Feb 23, 2005 5:22 pm Post subject:
Re: Compute in Oracle
|
|
|
I have no idea.
Not unless you make use of some pl/sql. |
|
| Back to top |
|
|
maya
*nix forums beginner
Joined: 16 Feb 2005
Posts: 25
|
| Posted: Wed Feb 23, 2005 5:19 pm Post subject:
Re: Compute in Oracle
|
|
|
yes, you are correct. Now I have a variable "average" which comes out
of the select statement. Is there a way to display this variable in the
location where I specify teach1 average in a sqlplus report ? |
|
| Back to top |
|
|
Mahesh Rajendran
*nix forums beginner
Joined: 03 May 2005
Posts: 24
|
| Posted: Wed Feb 23, 2005 5:11 pm Post subject:
Re: Compute in Oracle
|
|
|
I dont think so.
You cannot compute on a computed display ( you need some kind of
placeholder).
NOT with sql*plus. |
|
| Back to top |
|
|
maya
*nix forums beginner
Joined: 16 Feb 2005
Posts: 25
|
| Posted: Wed Feb 23, 2005 4:21 pm Post subject:
Re: Compute in Oracle
|
|
|
I will post my example one more time
Principal Teacher Student co su Percent
----------- ------------- ------------ --- --- ---
PRIN1 teach1 stud1 7 5 71
PRIN2 teach1 stud2 4 3 75
teach1 total 11 8
teach1 average 72.73
here 72.73 is the average and it is calcuated as: 8*100/11 [ from the
other totals]
Is there a way for me to display this average [ teach1 average
72.73
] in the correct location ? |
|
| Back to top |
|
|
Mahesh Rajendran
*nix forums beginner
Joined: 03 May 2005
Posts: 24
|
| Posted: Wed Feb 23, 2005 4:08 pm Post subject:
Re: Compute in Oracle
|
|
|
--
-- Used all the available functions
--
mag@mutation_mutation > get q
1 clear breaks
2 clear computes
3 set feed on
4 set term on
5 break on job
6 compute sum LABEL 'Total' -
7 avg LABEL 'Average'-
8 min LabEL 'Minimum'-
9 Max LABEL 'Maximum'-
10 of sal inc_sal comm on job
11* select job,ename,sal,(sal+100) inc_sal, nvl(comm,0) comm from emp
order by job ;
12 .
mag@mutation_mutation > @q
JOB ENAME SAL INC_SAL COMM
--------- ---------- ---------- ---------- ----------
ANALYST SCOTT 3000 3100 0
FORD 3000 3100 0
********* ---------- ---------- ----------
Average 3000 3100 0
Minimum 3000 3100 0
Maximum 3000 3100 0
Total 6000 6200 0
CLERK SMITH 800 900 0
ADAMS 1100 1200 0
MILLER 1300 1400 0
JAMES 950 1050 0
********* ---------- ---------- ----------
Average 1037.5 1137.5 0
Minimum 800 900 0
Maximum 1300 1400 0
Total 4150 4550 0
MANAGER JONES 2975 3075 0
CLARK 2450 2550 0
BLAKE 2850 2950 0
********* ---------- ---------- ----------
Average 2758.33333 2858.33333 0
Minimum 2450 2550 0
Maximum 2975 3075 0
Total 8275 8575 0
PRESIDENT KING 5000 5100 0
********* ---------- ---------- ----------
Average 5000 5100 0
Minimum 5000 5100 0
Maximum 5000 5100 0
Total 5000 5100 0
SALESMAN ALLEN 1600 1700 300
MARTIN 1250 1350 1400
TURNER 1500 1600 0
WARD 1250 1350 500
********* ---------- ---------- ----------
Average 1400 1500 550
Minimum 1250 1350 0
Maximum 1600 1700 1400
Total 5600 6000 2200 |
|
| Back to top |
|
|
Mahesh Rajendran
*nix forums beginner
Joined: 03 May 2005
Posts: 24
|
| Posted: Wed Feb 23, 2005 3:49 pm Post subject:
Re: Compute in Oracle
|
|
|
Unfortunately , your example IS NOT READABLE.
I dont get a thing out of it.
mag@mutation_mutation > compute avg of comm on job
mag@mutation_mutation > compute sum of sal inc_sal on job
mag@mutation_mutation > break on job
mag@mutation_mutation > /
JOB ENAME SAL INC_SAL COMM
--------- ---------- ---------- ---------- ----------
ANALYST SCOTT 3000 3100 0
FORD 3000 3100 0
********* ---------- ---------- ----------
avg 0
sum 6000 6200
CLERK SMITH 800 900 0
ADAMS 1100 1200 0
MILLER 1300 1400 0
JAMES 950 1050 0
********* ---------- ---------- ----------
avg 0
sum 4150 4550
MANAGER JONES 2975 3075 0
CLARK 2450 2550 0
BLAKE 2850 2950 0
********* ---------- ---------- ----------
avg 0
sum 8275 8575
PRESIDENT KING 5000 5100 0
********* ---------- ---------- ----------
avg 0
sum 5000 5100
SALESMAN ALLEN 1600 1700 300
MARTIN 1250 1350 1400
TURNER 1500 1600 0
WARD 1250 1350 500
********* ---------- ---------- ----------
avg 550
sum 5600 6000
14 rows selected. |
|
| Back to top |
|
|
Google
|
|
| Back to top |
|
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
Forum index
»
Databases
»
Oracle
»
Tools
