Difficult SQL Please Help

From: Sean Harasin <sharasin_at_mbakercorp.com>
Date: Sun, 20 Feb 2000 17:23:28 GMT
Message-ID: <kqVr4.46$RE2.327756@dca1-nnrp1.news.digex.net>

I've been at this for quite a while, I'm so close yet so far.........

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.

HERE IS THE STATEMENT: SELECT

     READING_DATE AS READ_DATE,
     MIN(reading_time) AS READ_TIME,
     SUM(DECODE(INSTRUMENT_NAME, 'P001', (3.92 / ((DECODE(HEADWATER -
TAILWATER, 0, NULL,(HEADWATER-TAILWATER))) / 100)))) AS "P001 DELTA_HEAD",
     LOC_CODE
FROM
     PIEZ.P_READINGS R1

WHERE
(READING_DATE <= TO_DATE('12/31/1968', 'MM/DD/YYYY')) AND
(READING_DATE >= TO_DATE('01/31/1968', 'MM/DD/YYYY')) AND
(UPPER(LOC_CODE) = 'ALG') AND
(INSTRUMENT_NAME IN ('P001', 'P002')) AND
    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')) GROUP BY
    READING_DATE,
    LOC_CODE
ORDER BY
    READING_DATE HERE IS THE FRAGMENT THAT SHOULD BE PUT IN PLACE OF THE 3.92: SUM((DECODE(INSTRUMENT_NAME, 'P002', WATER_SURFACE, 0) - DECODE(INSTRUMENT_NAME, 'P001', TAILWATER, 0))) Can Anybody Point Out What I Need To Do?

Thanks,
Sean Harasin
sharsin_at_mbakercorp.com Received on Sun Feb 20 2000 - 11:23:28 CST