Grant S - 9 months ago 33

SQL Question

I have a table that contains logged events for manufactured items. We consider each event as having 2 statuses that are based on detail and calculations from the prior logged events for the same item. So, I've developed a SELECT query that uses multiple self-joins to analyze factors from prior events relative to each event, and calculates the statuses. But because this query is relatively slow, I have added 2 status columns, and I want to UPDATE the columns with the calculated statuses well after the events happen. This way I can get fast reports later on the status columns instead of having to run all the calculations each time.

Here would be my table:

`CREATE TABLE ItemLog`

(

ItemID decimal(11) NOT NULL,

MessageTime DATE NOT NULL,

Temperature float(7),

Voltage float(7),

Status1 VARCHAR2(10 BYTE),

Status2 VARCHAR2(10 BYTE),

CONSTRAINT "ItemLog_PK" PRIMARY KEY ("ItemID ", "MessageTime ")

);

My SELECT calculation query is something like this:

`SELECT ItemID, MessageTime,`

CASE WHEN A.Voltage<B.Voltage and A.Voltage<C.Avg_Voltage and C.SD_Voltage<5 THEN 'Good' ELSE 'Bad' END Calculated_Status1,

CASE WHEN A.Temperature<B.Temperature and A.Temperature>C.Temperature and C.SD_Temperature>10 THEN 'Good' ELSE 'Bad' END Calculated_Status2

FROM ItemLog A,

(SELECT F.ItemID,

F.MessageTime Key_MessageTime,

S.Voltage,

S.Temperature

FROM ItemLog F,

ItemLog S

WHERE F.ItemID=S.ItemID

and S.MessageTime=

SELECT MAX(MessageTime)

FROM ItemLog

WHERE ItemID=F.ItemID

and MessageTime<F.MessageTime

and Voltage<12

and Temperature<125

) B, -- Returns the Voltage and Temperature from the prior time it was <12 and <125

(SELECT K.ItemID, K.MessageTime,

AVG(L.Temp) Avg_Temperature, STDDEV(L.Temperature) SD_Temp,

AVG(L.Voltage) Avg_Voltage, STDDEV(L.Voltage) SD_Voltage

FROM ItemLog K,

ItemLog L

WHERE K.ItemID=L.ItemID

and L.MessageTime=

SELECT MAX(MessageTime)

FROM ItemLog

WHERE ItemID=K.ItemID

and MessageTime<K.MessageTime

GROUP BY K.ItemID, K.MessageTime

) C -- Returns the Voltage and Temperature stats from all prior messages

(SELECT ItemID

FROM ItemLog

WHERE Voltage>40

) D -- Returns all ItemID where Voltage was ever >40, to exclude them

WHERE A.ItemID=B.ItemID and A.MessageTime=B.MessageTime

and A.ItemID=C.ItemID and A.MessageTime=C.MessageTime

and A.ItemID=D.ItemID(+) and D.ItemID IS NULL

So, the question is, how can I update the Status1 and Status2 columns in the table to be the Calculated_Status1 and Calculated Status2 columns? I've tried taking my calculation query and joining it to the table by the 2 primary keys, but I get the "ORA-01779: cannot modify a column which maps to a non key-preserved table" error.

`UPDATE (`

SELECT U.*,

V.Calculated_Status1

V.Calculated_Status2

FROM ItemLog U,

( <calculation query above> ) V

WHERE U.ItemID=V.ItemID and U.MessageTime=V.MessageTime )

SET U.Status1=V.CalculatedStatus1,

U.Status2=V.CalculatedStatus2

I could imagine an UPDATE with a

`SET Status1=(SELECT...`

Answer

I hope the solution you are looking for can be served up using MERGE statement. I hope the query which you have posted is correct. I have build the solution on top of the query. Let me know if this helps.

```
MERGE INTO ItemLog it USING
(SELECT ItemID, MessageTime,
CASE WHEN A.Voltage<B.Voltage and A.Voltage<C.Avg_Voltage and C.SD_Voltage<5 THEN 'Good' ELSE 'Bad' END Calculated_Status1,
CASE WHEN A.Temperature<B.Temperature and A.Temperature>C.Temperature and C.SD_Temperature>10 THEN 'Good' ELSE 'Bad' END Calculated_Status2
FROM ItemLog A,
(SELECT F.ItemID,
F.MessageTime Key_MessageTime,
S.Voltage,
S.Temperature
FROM ItemLog F,
ItemLog S
WHERE F.ItemID=S.ItemID
and S.MessageTime=
SELECT MAX(MessageTime)
FROM ItemLog
WHERE ItemID=F.ItemID
and MessageTime<F.MessageTime
and Voltage<12
and Temperature<125
) B, -- Returns the Voltage and Temperature from the prior time it was <12 and <125
(SELECT K.ItemID, K.MessageTime,
AVG(L.Temp) Avg_Temperature, STDDEV(L.Temperature) SD_Temp,
AVG(L.Voltage) Avg_Voltage, STDDEV(L.Voltage) SD_Voltage
FROM ItemLog K,
ItemLog L
WHERE K.ItemID=L.ItemID
and L.MessageTime=
SELECT MAX(MessageTime)
FROM ItemLog
WHERE ItemID=K.ItemID
and MessageTime<K.MessageTime
GROUP BY K.ItemID, K.MessageTime
) C -- Returns the Voltage and Temperature stats from all prior messages
(SELECT ItemID
FROM ItemLog
WHERE Voltage>40
) D -- Returns all ItemID where Voltage was ever >40, to exclude them
WHERE A.ItemID=B.ItemID and A.MessageTime=B.MessageTime
and A.ItemID=C.ItemID and A.MessageTime=C.MessageTime
and A.ItemID=D.ItemID(+) and D.ItemID IS NULL)z
ON
(it.ItemID = z.ItemID AND it.MessageTime = z.MessageTime)
WHEN MATCHED THEN
UPDATE SET it.STATUS1 = z.Calculated_Status1,
it.STATUS2 = z.Calculated_Status2;
```