| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL or PL/SQL
Not so harder; there's just a little modification of my previous query:
select substr(to_char(trunc(timestamp-21599/86400), 'DD-MON-YYYY'),1,11) "Date",
decode(trunc((to_number(to_char(timestamp,'SSSSS'))-21599+
decode(sign(to_number(to_char(timestamp,'SSSSS'))-21599),
-1, 86400, 0))/43200),
0, 'Yes', 'No') "Day",
sum(decode(instr(alarm_name, '-co-'), 0, 0,
decode(severity,1,1,0))) "CO Sev 1",
sum(decode(instr(alarm_name, '-co-'), 0, 0,
decode(severity,2,1,0))) "CO Sev 2",
sum(decode(instr(alarm_name, '-co-'), 0, 0,
decode(severity,3,1,0))) "CO Sev 3",
sum(decode(instr(alarm_name, '-pe-'), 0, 0,
decode(severity,1,1,0))) "PE Sev 1",
sum(decode(instr(alarm_name, '-pe-'), 0, 0,
decode(severity,2,1,0))) "PE Sev 2",
sum(decode(instr(alarm_name, '-pe-'), 0, 0,
decode(severity,3,1,0))) "PE Sev 3",
sum(decode(instr(alarm_name, '-ce-'), 0, 0,
decode(severity,1,1,0))) "CE Sev 1",
sum(decode(instr(alarm_name, '-ce-'), 0, 0,
decode(severity,2,1,0))) "CE Sev 2",
sum(decode(instr(alarm_name, '-ce-'), 0, 0,
decode(severity,3,1,0))) "CE Sev 3",
sum(decode(instr(alarm_name, '-ds-'), 0, 0,
decode(severity,1,1,0))) "DS Sev 1",
sum(decode(instr(alarm_name, '-ds-'), 0, 0,
decode(severity,2,1,0))) "DS Sev 2",
sum(decode(instr(alarm_name, '-ds-'), 0, 0,
decode(severity,3,1,0))) "DS Sev 3"
from alarm
trunc((to_number(to_char(timestamp,'SSSSS'))-21599+
decode(sign(to_number(to_char(timestamp,'SSSSS'))-21599),
-1, 86400, 0))/43200)
/
--
Have a nice day
Michel
Vigi98 <hubera_at_caramail.com> a écrit dans le message :
Uux34.1211$ko1.7515156_at_nnrp1.proxad.net...
> Harder and harder !
>
> My alarms come from several network equipment (you are going to know all the
> project I'm working on!). I would like to obtain the same result as before
> but split, for each semi-day, in four parts : alarms from equipment whose
> names are like '%-co-%', alarms from equipment whose names are like
> '%-pe-%', alarms from equipment whose names are like '%-ce-%' and alarms
> from equipment whose names are like '%-ds-%'.
>
> Thus I would obtain a result with the following fields :
> date of the first part of the 12h period ; flag to indicate if the hour of
> the alarm is between 6AM and 6PM or not ; number of alarms of severity 1 for
> co equipments; number of alarms of severity 2 for co equipments; number of
> alarms of severity 3 for co equipments; number of alarms of severity 1 for
> pe equipments; number of alarms of severity 2 for pe equipments; number of
> alarms of severity 3 for pe equipments; number of alarms of severity 1 for
> ce equipments; number of alarms of severity 2 for ce equipments; number of
> alarms of severity 3 for ce equipments; number of alarms of severity 1 for
> ds equipments; number of alarms of severity 2 for ds equipments; number of
> alarms of severity 3 for ds equipments;
>
> If someone has enough courage to find a solution...
>
> Thanks in advance.
>
>
> vigi98_at_my-deja.com a écrit dans le message <825mku$37j$1_at_nnrp1.deja.com>...
> >Hi all,
> >
> >I'm not very good at doing SQL requests or PL/SQL scripts. Could you
> >solve the following problem :
> >
> >I've got three fields in a table : alarm_id,date-hour and severity.
> >Severity is an integer between 1 and 3.
> >
> >What I would like to obtain is what follows : for each period of 12h
> >(from 6AM to 6PM, then from 6PM to 6AM) I would have the number of
> >alarms per severity number.
> >
> >Typically, it would give a table with the following fields :
> >
> >date of the first part of the 12h period ; flag to indicate if the hour
> >of the alarm is between 6AM and 6PM or not ; number of alarms of
> >severity 1 ; number of alarms of severity 2 ; number of alarms of
> >severity 3
> >
> >
> >Thank you very much for your answers
> >
> >
> >Sent via Deja.com http://www.deja.com/
> >Before you buy.
>
>
Received on Thu Dec 09 1999 - 04:18:19 CST
 |
 |