Update con tres tablas

[elarys]

Hola estoy migrando de mysql a firebird y me trabe en un simple update

Tengo estas 3 tablas las resumo para el caso


CREATE TABLE CATEGORIA
(
IDGRUPO Smallint NOT NULL,
GRUPOS Varchar(10),
RESTAR Time,
PRIMARY KEY (IDGRUPO)
);

CREATE TABLE REGISTRO
(
CODIGO Smallint NOT NULL,
NOMBRE Varchar(20),
GRUPO Smallint,
TIEMPO Time,
PRIMARY KEY (CODIGO)
);

CREATE TABLE TIEMPOS
(
IDTIME Smallint NOT NULL,
TIEMPO Time,
CODIGO Smallint,
PRIMARY KEY (IDTIME)
);


Esta es mi consulta en MySql 5.5 que funciona correctamente

UPDATE REGISTRO R
INNER JOIN CATEGORIA C ON (R.GRUPO = C.IDGRUPO)
INNER JOIN TIEMPOS T ON (R.CODIGO = T.CODIGO)
SET R.TIEMPO = (T.TIEMPO - C.RESTAR)
WHERE R.CODIGO > 0


Pero en firebird me dice que el INNER JOIN da el siguiente error

Error: *** IBPP::SQLException ***
Context: Statement::Prepare( UPDATE REGISTRO R
INNER JOIN CATEGORIA C ON (R.GRUPOS = C.GRUPOS)
INNER JOIN TIEMPOS T ON (R.NUMERO = T.NUMERO)
SET R.TIEMPO = (T.TIEMPO - C.RESTAR)
WHERE R.NUMERO > 0 )
Message: isc_dsql_prepare failed

SQL Message : -104
Invalid token

Engine Code : 335544569
Engine Message :
Dynamic SQL Error
SQL error code = -104
Token unknown - line 2, column 1
INNER

Me dijeron por ahi que use subconsultas, alguna idea

Ademas necesito que esa resta sea Formateada en (HH:MM:SS.ZZZ)
Hora, minutos, segundos y centesimas, osea pasarlo a campo de tipo Time

[lbuelvas]

Parece que debes solucionarlo con un procedimiento almacenado.

[lbuelvas]

SET TERM ^ ;

create or alter procedure SP_CLUB_DELPHI_01
as
declare variable CODIGO smallint;
declare variable TIEMPO_DIFERENCIA time;
begin
for select registro.codigo,
tiempos.tiempo - categoria.restar
from registro
inner join categoria on (registro.grupo = categoria.idgrupo)
inner join tiempos on (registro.codigo = tiempos.codigo)
into :codigo,
:tiempo_diferencia
do begin
update registro
set registro.tiempo = :tiempo_diferencia
where registro.codigo = :codigo;
end
end^
SET TERM ; ^

[elarys]

Gracias, pero me da el siguiente error

Message: isc_dsql_execute2 failed

SQL Message : -413
Overflow occurred during data type conversion.

Engine Code : 335544334
Engine Message :
conversion error from string "4753.6180"
At procedure 'DIFERENCIA' line: 8, col: 3

Probe esto y me da lo mismo de from string "4753.6180"

UPDATE REGISTRO R
SET R.TIEMPO = (
(SELECT T.TIEMPO FROM TIEMPOS T WHERE R.CODIGO = T.CODIGO) -
(SELECT C.RESTAR FROM CATEGORIA C WHERE R.GRUPO = C.IDGRUPO))
WHERE R.CODIGO > 0

[elarys]

Estoy pensando que tendria que hacer un CAST (Resultado) AS Time como seria?
Se puede agregar al procedimiento o a la consulta update que tire recien

[Casimiro Notevi]

Parece que ese dato es incorrecto, seguramente es: "4753,6180"

[elarys]

Estos son mis datos en Tabla Tiempos


1 | 01:24:13.618 | 52
2 | 01:54:13.618 | 53


Y estos en la tabla Categoria

1 | Grupo A | 00:05:00.000


El dato 4753.6180 al parecer es el dato
01:24:13 = 4753 supuestamente son segundos o no se

La parte decimal no se modifico 6180 = 618

[lbuelvas]

Bueno me puse en la tarea de investigación, muy interesante como se manejan los campos time en Firebird. Lo primero es que la diferencia entre dos datos de tipo type es un decimal(9,4), segundo para hacer la conversion de una resta tipo time1 -time2 es que time1 debe ser mayor o igual a time2 para evitar problemas con una conversion de un resultado negativo.

Para mayor información de firebird comprense el libro "The Firebird Book (Second Edition): A Reference for Database Developers".

Me fue de utilidad este hilo http://tech.groups.yahoo.com/group/firebird-support/message/97350 el cual utilicé y modifiqué una parte para el procedimiento adjunto, debes hacer pruebas con datos que contegan milésimas porque no alcancé a hacer las pruebas con datos conteniendo milésimas.

Tal vez haya una función que maneje directamente lo que necesitas pero no encontré, el código adjunto espero sea de utilidad, si les sirve me deben una pola (cerveza).

SET TERM ^ ;

create or alter procedure SP_CLUB_DELPHI_01
as
declare variable CODIGO smallint;
declare variable TIEMPO_FINAL time;
declare variable TIEMPO_DIFERENCIA double precision;
declare variable HORAS smallint;
declare variable MINUTOS smallint;
declare variable SEGUNDOS smallint;
declare variable MILESIMAS smallint;
begin
for select registro.codigo,
tiempos.tiempo - categoria.restar
from registro
inner join categoria on (registro.grupo = categoria.idgrupo)
inner join tiempos on (registro.codigo = tiempos.codigo)
into :codigo,
:tiempo_diferencia
do begin
-- La diferencia de dos datos de tipo time es el numero de segundos
-- en formato deciamal(9,4), debe entonces hacerse la conversion
horas = cast ((tiempo_diferencia / 3600.0) as smallint);
tiempo_diferencia = tiempo_diferencia - (horas * 3600);
minutos = cast ((tiempo_diferencia / 60.0) as smallint);
tiempo_diferencia = tiempo_diferencia - (minutos * 60);
segundos = cast ((tiempo_diferencia ) as smallint);
tiempo_diferencia = tiempo_diferencia - (segundos );
milesimas = cast (tiempo_diferencia * 1000 as smallint);

tiempo_final = cast
(
cast(horas as varchar(2))
|| ':' || cast(minutos as varchar(2))
|| ':' || cast(segundos as varchar(2))
|| ':' || cast(milesimas as varchar(4))
as time
);
update registro
set registro.tiempo = :tiempo_final
where registro.codigo = :codigo;
end
end

^

SET TERM ; ^

[elarys]

Gracias en cuanto pueda lo pruebo, pero de tanto no darme resultados y perdida de tiempo. Me dije a programar se ha dicho, tomando los valores recorriendo las tablas, restar y luego con ese valor un simple Update. Calculo que lleva mucho mas tiempo que con la consulta que todavia no termine de resolver. Pero por ahora esta funcionando perfectamente, lo intentare por ultima vez con el ultimo post de lbuelvas y si no se quedara a codigo nomas. Aunque lo he probado con una base de datos que tengo de prueba, donde tengo unos 10 a 30 registros. Vere que pasa cuando lo pruebe en produccion de 400 a 500 registros.