Re: SQL or PL/SQL

From: <rtproffitt_at_my-deja.com>
Date: Fri, 10 Dec 1999 16:38:02 GMT
Message-ID: <82ra99$4m5$1@nnrp1.deja.com>

Likewise, a simple modification in my approach also....
(Prepare a sort key based on equipment name...)

Select

   Decode(instr(EquipName,'-ce-'), 0,

     Decode(instr(EquipName,'-co-'), 0,
       Decode(instr(EquipName,'-ds-'), 0,
         Decode(instr(EquipName,'-pe-'), 0, 'UNK',
          'PE'),
        'DS'),
      'CO'),

    'CE') EquipKey,
   EquipName,
  Decode (
    trunc( to_number( to_char(date_hour,'hh24.mi'))/6 ),     0, to_char( date_hour - 1, 'yyyymmdd')||'PM',

      Decode (
        trunc( to_number( to_char(date_hour,'hh24.mi'))/18 ),
        0, to_char( date_hour, 'yyyymmdd')||'AM',
            to_char( date_hour, 'yyyymmdd')||'PM') ) TimeFlag,

  Severity,
  Count(alarm_id) AlarmCnt
From TableName
Group By
  Decode (
    trunc( to_number( to_char(date_hour,'hh24.mi'))/6 ),     0, to_char( date_hour - 1, 'yyyymmdd')||'PM',

      Decode (
        trunc( to_number( to_char(date_hour,'hh24.mi'))/18 ),
        0, to_char( date_hour, 'yyyymmdd')||'AM',
            to_char( date_hour, 'yyyymmdd')||'PM') ),

  Severity
Order by 1,3,4

Robert Proffitt
Beckman Coulter

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Dec 10 1999 - 10:38:02 CST