SQL Help

From: Guidry, Chris <chris.guidry_at_atcoelectric.com>
Date: Fri, 13 Sep 2002 14:08:28 -0800
Message-ID: <F001.004CFF9F.20020913140828@fatcity.com>

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 subscribing). Received on Fri Sep 13 2002 - 17:08:28 CDT