Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: SQL Help

RE: SQL Help

From: Guidry, Chris <chris.guidry_at_atcoelectric.com>
Date: Mon, 16 Sep 2002 09:13:25 -0800
Message-ID: <F001.004D0A18.20020916091325@fatcity.com>


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_at_atcoelectric.com



> -----Original Message-----
> From: Viral Desai [SMTP:viral303_at_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" <chris.guidry_at_atcoelectric.com>
> >Reply-To: ORACLE-L_at_fatcity.com
> >To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> >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_at_atcoelectric.com
> >
> >
> >--
> >Please see the official ORACLE-L FAQ: http://www.orafaq.com
> >--
> >Author: Guidry, Chris
> > INET: chris.guidry_at_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_at_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_at_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_at_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_at_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_at_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).
Received on Mon Sep 16 2002 - 12:13:25 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US