| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: GROUP & NON-GROUP together?
Hi !
You usually use group by for group function such as sum, avg etc.
What you want is probably distinct to avoid duplicates in your report.
select distinct col1, col2 etc ..... and take off the group by.
About the ttitle, it affects every select statment in your script, that is
why you are seeing two ttitles.
What you need to do is start spooling at the point where you want the
report to start and show the ttitle.
Since you say you are learning SQL*Plus, here is a standard template that I have made up for our department. Change the table names and column names and you should get a good report. it is for the UNIX platform.
It has some other neet features too.
set termout on pause off doc off
prompt
prompt
prompt This program generates a report for meters where the Integration
count
prompt and the Pressure count matches any records in table CHARTINPUT.
prompt You will be prompted to enter these numbers.
prompt
prompt To use the Default values given in brackets, press the ENTER key
without
prompt entering any value for the prompts.
prompt
prompt
prompt Do you want to run this report ? Enter Y or N.
prompt
prompt
whenever sqlerror exit
column answer new_value answer
accept answer char prompt 'Enter Y or N [ N]: '
set termout off
select nvl( upper('&answer'), 'N') answer
from dual
/
set termout on verify off heading off feedback off
prompt
select 'Exiting without generating report per your request !!!'
from dual
where '&answer' != 'Y'
/
/*
Seems to me that SQL*Menu does not recognize the WHENEVER SQLERROR
SQL*Plus command. Need to do the following to get out of SQL*Plus.
*/
prompt
select 'Please type any letter and press ENTER key !!!'
from dual
where '&answer' != 'Y'
/
prompt
set termout off
select 1/0
from dual
where '&answer' != 'Y'
/
set termout on
column integration new_value integration column pressure new_value pressure
accept integration char prompt 'Enter Integration count [ 0]: '
prompt
accept pressure char prompt 'Enter Pressure count [ 0]: '
prompt
prompt
prompt Sending report to your printer. Will take a few seconds. Please
wait ...
set termout off
select nvl( '&integration', '0') integration,
nvl( '&pressure', '0') pressure
from dual
/
set termout off pause off feedback off verify off heading on set linesize 80 newpage 0 pagesize 58 space 4
break on station skip 1
column today new_value today noprint
column time new_value time noprint
column station format a10 heading ' STATION'
column meter_id format 999990 heading 'METER |ID '
column entered format a11 heading 'ENTER DATE'
column onn format a11 heading ' ON DATE'
column seqcode format 90 heading 'SEQ'
column integration format 9999990 heading 'INTEGRATION|COUNT'
column pressure format 9999990 heading 'PRESSURE|COUNT'
select to_char(sysdate, 'DD-MON-YYYY') today,
to_char(sysdate, 'HH:MI:SS AM') time
from dual;
/*
Remove report file if present, otherwise in case of problem, you may
receive a printout of an old file.
*/
host rm -f $HOME/rep/check_charts_exist.lst > /dev/null
spool $HOME/rep/check_charts_exist.lst
ttitle today center 'THE FOLLOWING CHARTS EXIST FOR' -
right 'Page ' format 990 sql.pno skip 1 - time center 'Integration count = &integration and Pressure count = &pressure'-
skip 2 check_charts_exist.sql skip 2
select s.station, ci.meter_id,
to_char(ci.onn, 'dd-MON-yyyy') onn, ci.seqcode,
to_char(ci.entered, 'dd-MON-yyyy') entered
from stationbus s,
chartinput ci
where ci.meter_id = s.meter_id
select 'NO RECORDS', 99999999999,
'###########', 9999,
'###########'
from stationbus s,
chartinput ci
where ci.meter_id = s.meter_id
and integration = &integration
prompt. ********** END OF REPORT **********
spool off
host chmod 777 $HOME/rep/check_charts_exist.lst 1>/dev/null 2>/dev/null
host spo $HOME/rep/check_charts_exist.lst 1>/dev/null 2>/dev/null
/*
Host command always takes you to Bourne shell rather than C-shell. Wait for 5 minutes and remove the spooled file from the $HOME/rep directory.
If you "set termout on" in this script, the following gives an error
"expected symbol name is missing", but works OK.
*/
host (nohup /usr/bin/sleep 300 > /dev/null;/usr/bin/rm
$HOME/rep/check_charts_exis
t.lst > /dev/null) &
set termout on
prompt
accept answer char prompt 'Please press ENTER key to continue !!! '
prompt
exit
Hope this helps !!!
Oracleguru
www.oracleguru.net
oracleguru_at_mailcity.com
fpuhan_at_my-dejanews.com wrote in article
<7a3089$iac$1_at_nnrp1.dejanews.com>...
> Pardon me if this has been asked before. I'm attempting to teach myself
SQL*
> Plus. > > I'd like to be able to perform something along this line: > > column date_var noprint new_value today > column instnc noprint new_value curr_sid > ttitle left 'Date: ' today center 'Triggers' right curr_sid > > select > t.owner, > t.trigger_type, > t.trigger_event, > to_char(sysdate,'MM/DD/YY') date_var, > i.instance instnc > from > sys.dba_triggers t, > v$thread i > group by > t.owner, > t.trigger_type > / > > but I receive the error message > ORA-00979 not a GROUP BY expression > > if I add to the 'group by' clause > i.instance > > the query completes but my ttitle variables remain blank. > > If I make a separate query such as > > select > to_char(sysdate,'MM/DD/YY') date_var, > i.instance instnc > from > v$thread > / > > the finished product (report) has the proper ttitle, but I wind up with > duplicate report ttitles. I can 'set term off' and hide it from thescreen
> display, but not the printed report. > > Is there a way to do what I've attempted to explain here? > > Thanks in advance. > > -----------== Posted via Deja News, The Discussion Network ==---------- > http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
> Received on Mon Feb 15 1999 - 09:38:33 CST
 |
 |