Return-path: Envelope-to: oracle-l@orafaq.net Delivery-date: Tue, 17 Sep 2002 01:06:10 +0800 Received: from newsfeed.cts.com ([209.68.248.164]) by jean.onefuse.com with esmtp (Exim 3.36 #1) id 17qzK6-0007TF-00 for oracle-l@orafaq.net; Tue, 17 Sep 2002 01:06:10 +0800 Received: from fatcity.UUCP (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id KAA04533; Mon, 16 Sep 2002 10:06:07 -0700 (PDT) Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 004D0A18; Mon, 16 Sep 2002 09:13:25 -0800 Message-ID: Date: Mon, 16 Sep 2002 09:13:25 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: "Guidry, Chris" Sender: root@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: "Guidry, Chris" Subject: RE: SQL Help Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Thank you. That did the trick. -- Chris J. Guidry P.Eng. EE ATCO Electric, Metering Services Phone: (780) 420-4142 Fax: (780) 420-3854 Email: chris.guidry@atcoelectric.com > -----Original Message----- > From: Viral Desai [SMTP:viral303@hotmail.com] > Sent: Saturday, September 14, 2002 08:33 AM > To: Multiple recipients of list ORACLE-L > Subject: Re: SQL Help > > Chris, > > How about following? is that what you are looking for: > > Cheers > Viral > > select serialnumber, type, max(jan), max(feb), ..... > from > ( > SELECT mm.serialnumber, m.type > ,DECODE(TO_CHAR(mm.readdt, 'MON'), 'JAN', MAX(ROUND(mm.data1, 2))) JAN > ,DECODE(TO_CHAR(mm.readdt, 'MON'), 'FEB', MAX(ROUND(mm.data1, 2))) FEB > ,DECODE(TO_CHAR(mm.readdt, 'MON'), 'MAR', MAX(ROUND(mm.data1, 2))) MAR > ,DECODE(TO_CHAR(mm.readdt, 'MON'), 'APR', MAX(ROUND(mm.data1, 2))) APR > ,DECODE(TO_CHAR(mm.readdt, 'MON'), 'MAY', MAX(ROUND(mm.data1, 2))) MAY > ,DECODE(TO_CHAR(mm.readdt, 'MON'), 'JUN', MAX(ROUND(mm.data1, 2))) JUN > ,DECODE(TO_CHAR(mm.readdt, 'MON'), 'JUL', MAX(ROUND(mm.data1, 2))) JUL > ,DECODE(TO_CHAR(mm.readdt, 'MON'), 'AUG', MAX(ROUND(mm.data1, 2))) AUG > FROM metera m, meterm mm, serialnumber s > WHERE mm.dataLid=111 > AND mm.readtype = 1 > AND TRUNC(mm.readdt) > TO_DATE('31-DEC-2001','DD-MON-YYYY') > AND TRUNC(mm.readdt) < TO_DATE('01-SEP-2002','DD-MON-YYYY') > AND mm.port = 0 > AND m.port = 0 > AND (s.id = 31 OR s.id = 33) > AND mm.serialnumber = s.serialnumber > AND m.serialnumber = mm.serialnumber > GROUP BY mm.serialnumber, m.type, TO_CHAR(mm.readdt, 'MON') > ORDER BY mm.serialnumber, m.type, TO_DATE(TO_CHAR(mm.readdt, 'MON'), > 'MON') > ) > group by serialnumber, type; > > > > >From: "Guidry, Chris" > >Reply-To: ORACLE-L@fatcity.com > >To: Multiple recipients of list ORACLE-L > >Subject: SQL Help > >Date: Fri, 13 Sep 2002 14:08:28 -0800 > > > >Hi All, > >Any ideas how I can get this into a tabular form (i.e. one row per > >serialnumber)? > >There may be gaps (nulls) anywhere in the data for a given month. > >I've tried with PL/SQL but it's difficult to account for gaps. > >The following is the closest I've come. > >O7345 > >TGIF > > > > 1 SELECT mm.serialnumber, m.type > > 2 ,DECODE(TO_CHAR(mm.readdt, 'MON'), 'JAN', MAX(ROUND(mm.data1, 2))) > >JAN > > 3 ,DECODE(TO_CHAR(mm.readdt, 'MON'), 'FEB', MAX(ROUND(mm.data1, 2))) > >FEB > > 4 ,DECODE(TO_CHAR(mm.readdt, 'MON'), 'MAR', MAX(ROUND(mm.data1, 2))) > >MAR > > 5 ,DECODE(TO_CHAR(mm.readdt, 'MON'), 'APR', MAX(ROUND(mm.data1, 2))) > >APR > > 6 ,DECODE(TO_CHAR(mm.readdt, 'MON'), 'MAY', MAX(ROUND(mm.data1, 2))) > >MAY > > 7 ,DECODE(TO_CHAR(mm.readdt, 'MON'), 'JUN', MAX(ROUND(mm.data1, 2))) > >JUN > > 8 ,DECODE(TO_CHAR(mm.readdt, 'MON'), 'JUL', MAX(ROUND(mm.data1, 2))) > >JUL > > 9 ,DECODE(TO_CHAR(mm.readdt, 'MON'), 'AUG', MAX(ROUND(mm.data1, 2))) > >AUG > > 10 FROM metera m, meterm mm, serialnumber s > > 11 WHERE mm.data1id=111 > > 12 AND mm.readtype = 1 > > 13 AND TRUNC(mm.readdt) > TO_DATE('31-DEC-2001','DD-MON-YYYY') > > 14 AND TRUNC(mm.readdt) < TO_DATE('01-SEP-2002','DD-MON-YYYY') > > 15 AND mm.port = 0 > > 16 AND m.port = 0 > > 17 AND (s.id = 31 OR s.id = 33) > > 18 AND mm.serialnumber = s.serialnumber > > 19 AND m.serialnumber = mm.serialnumber > > 20 GROUP BY mm.serialnumber, m.type, TO_CHAR(mm.readdt, 'MON') > > 21 ORDER BY mm.serialnumber, m.type, TO_DATE(TO_CHAR(mm.readdt, > 'MON'), > >'MON'); > > > >SERIALNUMBER TYPE JAN FEB MAR APR MAY JUN > >JUL AUG > >------------ ----------- --------- --------- --------- --------- > --------- > >--------- --------- --------- > > 35891 X47693 > >67.94 > > 36515 X47007 2504.23 > > 36515 X47007 2773.35 > > 36515 X47007 3016.51 > > 36515 X47007 3182.65 > > 36515 X47007 > 3286.03 > > 36515 X47007 > >3432.04 > > 36515 X47007 > >3520.23 > > 36524 X07205 2101.18 > > 36524 X07205 2279.76 > > 36524 X07205 2448.01 > > 36524 X07205 2621.93 > > 36524 X07205 > 2753.39 > > 36524 X07205 > >2834.15 > > 36524 X07205 > >2917.87 > > 36524 X07205 > >2991.37 > > 36525 X47007 1642.29 > > 36525 X47007 0 > > 36525 X47007 1922.61 > > 36525 X47007 2056.58 > > 36525 X47007 > 2209.88 > > 36525 X47007 > >0 > > 36525 X47007 > >2532.08 > > 36525 X47007 > >2706.53 > > 36527 X17007 743.32 > > 36527 X17007 807.93 > > 36527 X17007 871.34 > > 36527 X17007 939.58 > > 36527 X17007 > 1010.61 > > 36527 X17007 > >1093.55 > > 36527 X17007 > >1199.93 > > 36527 X17007 > >1290.5 > > > > > > > > > >-- > >Chris J. Guidry P.Eng. EE > >ATCO Electric, Metering Services > >Phone: (780) 420-4142 > >Fax: (780) 420-3854 > >Email: chris.guidry@atcoelectric.com > > > > > >-- > >Please see the official ORACLE-L FAQ: http://www.orafaq.com > >-- > >Author: Guidry, Chris > > INET: chris.guidry@atcoelectric.com > > > >Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > >San Diego, California -- Public Internet access / Mailing Lists > >-------------------------------------------------------------------- > >To REMOVE yourself from this mailing list, send an E-Mail message > >to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in > >the message BODY, include a line containing: UNSUB ORACLE-L > >(or the name of mailing list you want to be removed from). You may > >also send the HELP command for other information (like subscribi > > _________________________________________________________________ > MSN Photos is the easiest way to share and print your photos: > http://photos.msn.com/support/worldwide.aspx > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Viral Desai > INET: viral303@hotmail.com > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing Lists > -------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Guidry, Chris INET: chris.guidry@atcoelectric.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).