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

"MarkusSchaber" <msr (AT) soloplan (DOT) de> a écrit dans le message de news:
40ee696d-3754-4916-849f-a848aa44ed52...oglegroups.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

Hi, Michel,

On 4 Okt., 10:11, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
But I need the two-level subquery here to apply the windowing function
(to limit to the first row).

Do you have any idea how to solve my problem then?

Thanks,
Markus

Hi,

On 4 Okt., 09:45, MarkusSchaber <m... (AT) soloplan (DOT) de> wrote:
An addition: I think I cannot use analytical functions directly inside
the outer query to get the reference value, because the newest
previous row which had measure=0 may be outside of the interval
specified by :startTime and :endTime.

thanks,
markus