SQL & PL/SQL

Hello All,

I'm trying to update several colums of a table from a inner join query.
First a retrieve the rows afected and the values that I need for the update (I call this subquery ED_Query).
It's important to note that this subquery has a group by and and having clause.

My first attemp (using the query that work in SQL Server query) fails:

SQL> update ED_Update
2 set ED_Update.dtHoraInicioReal = ED_Query.dtHoraInicioReal,
3 ED_Update.dtHoraFinReal = ED_Query.dtHoraFinReal,
4 ED_Update.fPorcentajeRealizado = ED_Query.fPorcentajeRealizado
5 from HISTORICOS_AVANZA.HSAE_HIS_EXPEDICIONDIARIA ED_Update
6 inner join (
7 select distinct ED.iIdExpedicion, ED.iIdExpedicionDiaria,
8 MAX(PT.iOrdenEnTrayecto) + 1 as iNumParadas,
9 MAX(HPP.iOrden) as iOrdenUltimaParada,
10 MIN(dtHora_LlegadaReal + iTiempoEnParada/(24*60*60)) as dtHoraInicioReal,
11 MAX(dtHora_LlegadaReal) as dtHoraFinReal,
12 100 * cast ((MAX(HPP.iOrden) + 1) as float) / cast ((MAX(PT.iOrdenEnTrayecto) + 1) as float) as fPorcentajeRealizado
13 from HISTORICOS_AVANZA.HSAE_HIS_EXPEDICIONDIARIA ED
14 left join HISTORICOS_AVANZA.HSAE_HIS_HORAPASOPARADA HPP
15 on ED.iIdExpedicion = HPP.iIdExpedicion and ED.dtJornada = HPP.dtJornada
16 left join AVANZA.SAE_URB_PARADASTRAYECTO PT on ED.iIdLinea = PT.iIdLinea and ED.iIdTrayecto = PT.iIdTrayecto
17 where ED.dtJornada = TO_DATE('14/01/2013', 'DD/MM/YYYY') and ED.iIdExpedicion in (-131076)
18 group by ED.iIdExpedicion, ED.iIdExpedicionDiaria, ED.dtHoraInicioReal, ED.dtHoraFinReal
19 having ED.dtHoraInicioReal <> min(dtHora_LlegadaReal + iTiempoEnParada/(24*60*60))
20 or ED.dtHoraFinReal <> max(dtHora_LlegadaReal)
21 ) ED_Query
22 on ED_Update.iIdExpedicionDiaria = ED_Query.iIdExpedicionDiaria;

ERROR at line 5:
ORA-00933: SQL command not properly ended

The subquery (ED_Query) work fine in Oracle, so I suspect that the problems are when I mix it with the update clause.

SQL> select distinct ED.iIdExpedicion, ED.iIdExpedicionDiaria,
2 MAX(PT.iOrdenEnTrayecto) + 1 as iNumParadas,
3 MAX(HPP.iOrden) as iOrdenUltimaParada,
4 MIN(dtHora_LlegadaReal + iTiempoEnParada/(24*60*60)) as dtHoraInicioReal,
5 MAX(dtHora_LlegadaReal) as dtHoraFinReal,
6 100 * cast ((MAX(HPP.iOrden) + 1) as float) / cast ((MAX(PT.iOrdenEnTrayecto) + 1) as float) as fPorcentajeRealizado,
7 ED.dtHoraInicioReal as ED_dtHoraInicioReal, ED.dtHoraFinReal as ED_dtHoraFinReal, ED.fPorcentajeRealizado as ED_fPorcentajeRealizado
8 from HISTORICOS_AVANZA.HSAE_HIS_EXPEDICIONDIARIA ED
9 left join HISTORICOS_AVANZA.HSAE_HIS_HORAPASOPARADA HPP
10 on ED.iIdExpedicion = HPP.iIdExpedicion and ED.dtJornada = HPP.dtJornada
11 left join AVANZA.SAE_URB_PARADASTRAYECTO PT on ED.iIdLinea = PT.iIdLinea and ED.iIdTrayecto = PT.iIdTrayecto
12 where ED.dtJornada = TO_DATE('14/01/2013', 'DD/MM/YYYY') and ED.iIdExpedicion in (-131076)
13 group by ED.iIdExpedicion, ED.iIdExpedicionDiaria, ED.dtHoraInicioReal, ED.dtHoraFinReal, ED.fPorcentajeRealizado
14 having ED.dtHoraInicioReal <> min(dtHora_LlegadaReal + iTiempoEnParada/(24*60*60))
15 or ED.dtHoraFinReal <> max(dtHora_LlegadaReal);

IIDEXPEDICION IIDEXPEDICIONDIARIA INUMPARADAS IORDENULTIMAPARADA DTHORAINI
------------- ------------------- ----------- ------------------ ---------
DTHORAFIN FPORCENTAJEREALIZADO ED_DTHORA ED_DTHORA ED_FPORCENTAJEREALIZADO
--------- -------------------- --------- --------- -----------------------
-131076 5662 406 15-JAN-13
15-JAN-13 15-JAN-13 15-JAN-13 0

-131076 5663 406 15-JAN-13
15-JAN-13 15-JAN-13 15-JAN-13 0

-131076 5664 406 15-JAN-13
15-JAN-13 15-JAN-13 15-JAN-13 0

After reading this forum, I change the query and try the next one:

SQL> UPDATE
2 (
3 select distinct ED.iIdExpedicion, ED.iIdExpedicionDiaria,
4 MAX(PT.iOrdenEnTrayecto) + 1 as iNumParadas,
5 MAX(HPP.iOrden) as iOrdenUltimaParada,
6 MIN(dtHora_LlegadaReal + iTiempoEnParada/(24*60*60)) as dtHoraInicioReal,
7 MAX(dtHora_LlegadaReal) as dtHoraFinReal,
8 100 * cast ((MAX(HPP.iOrden) + 1) as float) / cast ((MAX(PT.iOrdenEnTrayecto) + 1) as float) as fPorcentajeRealizado,
9 ED.dtHoraInicioReal as ED_dtHoraInicioReal, ED.dtHoraFinReal as ED_dtHoraFinReal, ED.fPorcentajeRealizado as ED_fPorcentajeRealizado
10 from HISTORICOS_AVANZA.HSAE_HIS_EXPEDICIONDIARIA ED
11 left join HISTORICOS_AVANZA.HSAE_HIS_HORAPASOPARADA HPP
12 on ED.iIdExpedicion = HPP.iIdExpedicion and ED.dtJornada = HPP.dtJornada
13 left join AVANZA.SAE_URB_PARADASTRAYECTO PT on ED.iIdLinea = PT.iIdLinea and ED.iIdTrayecto = PT.iIdTrayecto
14 where ED.dtJornada = TO_DATE('14/01/2013', 'DD/MM/YYYY') and ED.iIdExpedicion in (-131076)
15 group by ED.iIdExpedicion, ED.iIdExpedicionDiaria, ED.dtHoraInicioReal,ED.dtHoraFinReal, ED.fPorcentajeRealizado
16 having ED.dtHoraInicioReal <> min(dtHora_LlegadaReal + iTiempoEnParada/(24*60*60))
17 or ED.dtHoraFinReal <> max(dtHora_LlegadaReal)
18 )
19 SET ED_dtHoraInicioReal = dtHoraInicioReal,
20 ED_dtHoraFinReal = dtHoraFinReal,
21 ED_fPorcentajeRealizado = fPorcentajeRealizado;

ERROR at line 2:
ORA-01732: data manipulation operation not legal on this view

Some help?

Thanl in advance.

Edited by: 984483 on 28-ene-2013 1:48