Re: Oracle SQLPLUS Matrix report question

From: Mike Carney <mike_at_cray.com>
Date: 3 Dec 92 23:47:29 CST
Message-ID: <1992Dec3.234729.24298_at_hemlock.cray.com>

In article 40960_at_arctic.nprdc.navy.mil, vhall_at_nprdc.navy.mil (Valerie Hall) writes:
> I'am needing to produce a matrix report against Oracle tables using
> SQLPLUS. Can it be done? If so, can you show me some coding
> of how it is done? Thanks in advance.... Valerie.
>
>

I use decode function to produce matrix reports in SQLPLUS. It does require knowing in advance the values in the field being distributed horizontally. In the example below the distributed field is "severity". The performance is excellent even though syntactically it looks cumbersome.

select

	group_leader "Group leader",
	section_leader "Section leader",
    sum(decode(severity,'CRITICAL',1,0)) "Crit.", 
    sum(decode(severity,'URGENT',1,0)) "Urg.", 
    sum(decode(severity,'MAJOR',1,0)) "Maj.", 
    sum(decode(severity,'MINOR',1,0)) "Min.", 

    sum(decode(severity,'CRITICAL',0,'URGENT',0,'MAJOR',0,'MINOR',0,1)) "Other",     sum(decode(severity,'CRITICAL',1,'URGENT',1,'MAJOR',1,'MINOR',1,1)) "Total"   from spr_info
  group by

    	group_leader,
	section_leader; 

---

Mike Carney  mike_at_cray.com         Cray Research, Inc.    (612) 683-5635
		 
	 

Received on Fri Dec 04 1992 - 06:47:29 CET