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>
group by
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-5635Received on Fri Dec 04 1992 - 06:47:29 CET