Re: Problem with nested subquery

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Mon, 4 Oct 2010 10:11:21 +0200
Message-ID: <4ca98c31$0$25611$426a34cc_at_news.free.fr>

"MarkusSchaber" <msr_at_soloplan.de> a écrit dans le message de news: 40ee696d-3754-4916-849f-a848aa44ed52_at_h7g2000yqn.googlegroups.com... Hi,

I have a simple table like the following:

CREATE TABLE TEST
(
  ID NUMBER(10, 0) NOT NULL
, VEHICLE VARCHAR2(20 CHAR) NOT NULL
, TIMESTAMP TIMESTAMP(6)
, MEASURE NUMBER NOT NULL
, AMOUNT NUMBER NOT NULL
, CONSTRAINT TEST_PK PRIMARY KEY (ID) ENABLE
)

Now my (wanted) query is like the following:

SELECT
            cast(trunc(testData.timestamp, 'MI') as timestamp) as minuteTimeStamp,

            MAX(testData.amount) as amount,
            MAX(case when testData.amount != 0 THEN
              testData.amount - (
                select amount from (
                  select beforeChargingStarts.amount amount,
                         row_number() over (order by timestamp DESC)
rownumber
                      from TEST beforeChargingStarts
                      where beforechargingstarts.measure = 0
                        AND testData.vehicle =
beforeChargingStarts.vehicle
                        AND testData.timestamp >=
beforeChargingStarts.timestamp
                  ) WHERE rownumber between 1 and 1
                )*3.6
              else 0 end ) as quantity,
            MAX(abs(testData.measure)) as measure
          FROM TEST testData
          WHERE testData.TimeStamp >= cast(:startTime as timestamp)
            and testData.TimeStamp <= cast(:endTime as timestamp)
            and testdata.vehicle = :vehicle
          GROUP BY cast(trunc(testData.timestamp, 'MI') as timestamp)

This query is meant to accumulate several data points within every minute. The columns are defined as follows:

- minuteTimeStamp: The timestamp for the whole minute.
- amount: The maximum of the amount column.
- measure: The maximum value of some measure our sensors measure.
- quantity: If the measure is 0 for the current row, we use 0. If it

is not 0, we need the difference between the current amount and the amount of the newest previous row (by time) which had measure=0.

Now the query above does not work, it has the following error: ORA-00904: "TESTDATA"."TIMESTAMP": ungültiger Bezeichner 00904. 00000 - "%s: invalid identifier" *Cause:
*Action:
Fehler in Zeile: 12 Spalte: 39

I always thought that I can refer to rows of outer tables inside subqueries, and did this sucessfully in other cases. Now, why does oracle not recognize it in this case? (We use 11g Enterprise Edition 11.1.0.7.0)

Thanks a lot,
Markus

---

You can refer to a column that is only one level outer.

Regards
Michel Received on Mon Oct 04 2010 - 03:11:21 CDT