Re: Difficult SQL Please Help

From: Pasi J Jouhikainen <jouhikai_at_cc.helsinki.fi>
Date: 21 Feb 2000 00:42:09 +0200
Message-ID: <88pqk1$qbk$1@kruuna.Helsinki.FI>

In <kqVr4.46$RE2.327756_at_dca1-nnrp1.news.digex.net> "Sean Harasin" <sharasin_at_mbakercorp.com> writes:

>The Statement Below Works Just Fine With The 3.92 Hard Coded In, However I
>Need To Replace The 3.92 With The Statement Fragment (also below). When I
>Add The Statement Fragment I Get The Error: ORA-00937: not a single-group
>group function

>If I remove the Sum() From the Fragment, The Query Will Run, But I Get
>Incorrect Results.

>Can Anybody Point Out What I Need To Do?

My first thought would be to put the "fragment" into a sub-query in the FROM clause of the main query, group the "fragment" by instrument_name, loc_code, reading_date and reading_time within the sub-query, call the sub-query R4, and join it to R1 by the grouped columns. This may not be a very elegant solution, and I have no way of testing whether it even works, but here's the query I would try first:

SELECT R1.READING_DATE                       AS READ_DATE,
       MIN (R1.reading_time)                 AS READ_TIME,
       SUM (DECODE (R1.INSTRUMENT_NAME, 'P001',
                    (R4.FRAGMENT /
                     ((DECODE (R1.HEADWATER - R1.TAILWATER, 0,
                               NULL, (R1.HEADWATER - R1.TAILWATER)
                               )
                       ) / 100))))           AS "P001 DELTA_HEAD",
       R1.LOC_CODE
FROM   PIEZ.P_READINGS R1,
       (SELECT R3.INSTRUMENT_NAME,
               R3.LOC_CODE,
               R3.READING_DATE,
               R3.READING_TIME,
               SUM (DECODE (R3.INSTRUMENT_NAME,
                            'P002', R3.WATER_SURFACE, 0) -
                    DECODE (R3.INSTRUMENT_NAME,
                            'P001', R3.TAILWATER, 0))
                                             AS FRAGMENT
        FROM   PIEZ.P_READINGS R3
        GROUP  BY R3.INSTRUMENT_NAME,
                  R3.LOC_CODE,
                  R3.READING_DATE,
                  R3.READING_TIME) R4

WHERE R1.READING_DATE <= TO_DATE ('12/31/1968', 'MM/DD/YYYY') AND R1.READING_DATE >= TO_DATE ('01/31/1968', 'MM/DD/YYYY') AND UPPER (R1.LOC_CODE) = 'ALG'
AND R1.INSTRUMENT_NAME IN ('P001', 'P002') AND R1.READING_TIME =

           (SELECT MIN (R2.READING_TIME)
            FROM   PIEZ.P_READINGS R2
            WHERE  R2.LOC_CODE = R1.LOC_CODE
            AND    R2.INSTRUMENT_NAME = R1.INSTRUMENT_NAME
            AND    TRUNC (R2.READING_DATE, 'DD') =
                   TRUNC (R1.READING_DATE, 'DD'))
AND    R4.INSTRUMENT_NAME = R1.INSTRUMENT_NAME
AND    R4.LOC_CODE = R1.LOC_CODE

AND R4.READING_DATE = R1.READING_DATE AND R4.READING_TIME = R1.READING_TIME

GROUP  BY R1.READING_DATE,
          R1.LOC_CODE
ORDER  BY R1.READING_DATE

Hope that helps - if not, maybe you can get some other ideas from it.

-Pasi
--

Pasi Jouhikainen              24h Phone:  +358-40-7310859
jouhikai_at_cc.helsinki.fi       24h Fax:    +358-42-7310859
Address:        Ylisrinne 1 As 33 / 02210 Espoo / Finland
     or:  351 2nd Ave Pl, #4 / Coralville, IA 52241 / USA

Received on Sun Feb 20 2000 - 16:42:09 CST