a ver, este es el procedimiento almacenado que tengo que cambiar…
create or replace functionsp_rep_JornadaDeTrabajo(p_perspective_idbigint,
p_empleadobigint,
p_jt_idbigint,
p_buquebigint,
p_pt_clavebigint)
returns table(
devengo_basico_desdetimestamp,
devengo_basico_hastatimestamp,
devengo_basico_tipotext,
devengo_complementario_desdetimestamp,
devengo_complementario_hastatimestamp,
devengo_complementario_tipotext,
tarifas_varias_desdetimestamp,
tarifas_varias_hastatimestamp,
tarifas_varias_tipotext,
lsi_desdetimestamp,
lsi_hastatimestamp,
lsi_tipotext,
acumulaciones_variables_desdetimestamp,
acumulaciones_variables_hastatimestamp,
acumulaciones_variables_tipotext,
primer_nombretext,
segundo_nombretext,
primer_apellidotext,
segundo_apellidotext,
empleado_clavebigint,
puesto_trabajo_clavebigint,
puesto_trabajo_descripciontext,
buque_clavebigint,
buque_descripciontext,
jt_idbigint,
jt_clavetext,
jt_descripciontext)as
$$
declare
query_str text;
begin
query_str := 'with devengo_basico as (select
co0060ca01scaq.desde devengo_basico_desde,
co0060ca01scaq.hasta devengo_basico_hasta,
co0030ca02scag.descripcion devengo_basico_tipo,
co0060ca01scaq.fk165_co0050ca02scaa_clave,
co0060ca01scaq.fk474_co0010ca01scaa_clave,
co0060ca01scaq.fk639_co0040ca02scaa_clave,
co0060ca01scaq.fk333_co0060ca01scaa_id,
co0060ca01scaq.fk503_co0030ca02scaa_clave
from
co0060ca01scaq
inner join
co0060ca01scag on
co0060ca01scag.fk503_co0030ca02scaa_clave =
co0060ca01scaq.fk503_co0030ca02scaa_clave and
co0060ca01scag.fk474_co0010ca01scaa_clave =
co0060ca01scaq.fk474_co0010ca01scaa_clave and
co0060ca01scag.fk639_co0040ca02scaa_clave =
co0060ca01scaq.fk639_co0040ca02scaa_clave and
co0060ca01scag.fk333_co0060ca01scaa_id = co0060ca01scaq.fk333_co0060ca01scaa_id
and
co0060ca01scag.removed = false
inner join
co0060ca01scae on
co0060ca01scae.fk503_co0030ca02scaa_clave =
co0060ca01scag.fk503_co0030ca02scaa_clave and
co0060ca01scae.fk474_co0010ca01scaa_clave =
co0060ca01scag.fk474_co0010ca01scaa_clave and
co0060ca01scae.fk639_co0040ca02scaa_clave =
co0060ca01scag.fk639_co0040ca02scaa_clave and
co0060ca01scae.removed = false
inner join
co0060ca01scab on
co0060ca01scab.fk474_co0010ca01scaa_clave =
co0060ca01scae.fk474_co0010ca01scaa_clave and
co0060ca01scab.fk639_co0040ca02scaa_clave =
co0060ca01scae.fk639_co0040ca02scaa_clave and
co0060ca01scab.removed = false
inner join
co0030ca02scaa on co0030ca02scaa.clave =
co0060ca01scae.fk503_co0030ca02scaa_clave and co0030ca02scaa.removed = false
inner join
co0030ca02scag on co0030ca02scag.clave =
co0030ca02scaa.fk537_co0030ca02scag_clave and co0030ca02scag.removed = false
inner join
getperspectivecontent(' || p_perspective_id || ')
on persp_id = co0060ca01scaq.perspective_id
where co0060ca01scaq.removed = false),
devengo_complementario as (select
co0060ca01scar.desde
devengo_complementario_desde,
co0060ca01scar.hasta
devengo_complementario_hasta,
co0030ca02scah.descripcion
devengo_complementario_tipo,
co0060ca01scar.fk165_co0050ca02scaa_clave,
co0060ca01scar.fk474_co0010ca01scaa_clave,
co0060ca01scar.fk639_co0040ca02scaa_clave,
co0060ca01scar.fk333_co0060ca01scaa_id,
co0060ca01scar.fk543_co0030ca02scab_clave
from
co0060ca01scar
inner join
co0060ca01scah on
co0060ca01scah.fk333_co0060ca01scaa_id = co0060ca01scar.fk333_co0060ca01scaa_id
and
co0060ca01scah.fk474_co0010ca01scaa_clave =
co0060ca01scar.fk474_co0010ca01scaa_clave and
co0060ca01scah.fk639_co0040ca02scaa_clave =
co0060ca01scar.fk639_co0040ca02scaa_clave and
co0060ca01scah.fk543_co0030ca02scab_clave =
co0060ca01scar.fk543_co0030ca02scab_clave and
co0060ca01scah.removed = false
inner join
co0060ca01scaf on
co0060ca01scaf.fk474_co0010ca01scaa_clave =
co0060ca01scah.fk474_co0010ca01scaa_clave and
co0060ca01scaf.fk639_co0040ca02scaa_clave =
co0060ca01scah.fk639_co0040ca02scaa_clave and
co0060ca01scaf.fk543_co0030ca02scab_clave =
co0060ca01scah.fk543_co0030ca02scab_clave and
co0060ca01scaf.removed = false
inner join
co0060ca01scab on
co0060ca01scab.fk474_co0010ca01scaa_clave =
co0060ca01scaf.fk474_co0010ca01scaa_clave and
co0060ca01scab.fk639_co0040ca02scaa_clave =
co0060ca01scaf.fk639_co0040ca02scaa_clave and
co0060ca01scab.removed = false
inner join
co0030ca02scab on co0030ca02scab.clave
= co0060ca01scaf.fk543_co0030ca02scab_clave and co0030ca02scab.removed = false
inner join
co0030ca02scah on co0030ca02scah.clave
= co0030ca02scab.fk539_co0030ca02scah_clave and co0030ca02scah.removed = false
inner join
getperspectivecontent(' ||
p_perspective_id || ') on persp_id = co0060ca01scar.perspective_id
where co0060ca01scar.removed = false),
tarifas_varias as (select
co0060ca01scas.desde tarifas_varias_desde,
co0060ca01scas.hasta tarifas_varias_hasta,
co0030ca02scai.descripcion tarifas_varias_tipo,
co0060ca01scas.fk165_co0050ca02scaa_clave,
co0060ca01scas.fk474_co0010ca01scaa_clave,
co0060ca01scas.fk639_co0040ca02scaa_clave,
co0060ca01scas.fk333_co0060ca01scaa_id,
co0060ca01scas.fk514_co0030ca02scac_clave
from
co0060ca01scas
inner join
co0060ca01scai on
co0060ca01scai.fk474_co0010ca01scaa_clave =
co0060ca01scas.fk474_co0010ca01scaa_clave and
co0060ca01scai.fk639_co0040ca02scaa_clave =
co0060ca01scas.fk639_co0040ca02scaa_clave and
co0060ca01scai.fk514_co0030ca02scac_clave =
co0060ca01scas.fk514_co0030ca02scac_clave and
co0060ca01scai.fk333_co0060ca01scaa_id = co0060ca01scas.fk333_co0060ca01scaa_id
and
co0060ca01scai.removed = false
inner join
co0060ca01scaj on
co0060ca01scaj.fk474_co0010ca01scaa_clave =
co0060ca01scai.fk474_co0010ca01scaa_clave and
co0060ca01scaj.fk639_co0040ca02scaa_clave =
co0060ca01scai.fk639_co0040ca02scaa_clave and
co0060ca01scaj.fk514_co0030ca02scac_clave =
co0060ca01scai.fk514_co0030ca02scac_clave and
co0060ca01scaj.removed = false
inner join
co0060ca01scab on
co0060ca01scab.fk474_co0010ca01scaa_clave =
co0060ca01scaj.fk474_co0010ca01scaa_clave and
co0060ca01scab.fk639_co0040ca02scaa_clave =
co0060ca01scaj.fk639_co0040ca02scaa_clave and
co0060ca01scab.removed = false
inner join
co0030ca02scac on co0030ca02scac.clave =
co0060ca01scaj.fk514_co0030ca02scac_clave and co0030ca02scac.removed = false
inner join
co0030ca02scai on co0030ca02scai.clave =
co0030ca02scac.fk552_co0030ca02scai_clave and co0030ca02scai.removed = false
inner join
getperspectivecontent(' || p_perspective_id ||
') on persp_id = co0060ca01scas.perspective_id
where co0060ca01scas.removed = false),
lsi as (select
co0060ca01scau.desde lsi_desde,
co0060ca01scau.hasta lsi_hasta,
co0030ca02scak.descripcion lsi_tipo,
co0060ca01scau.fk165_co0050ca02scaa_clave,
co0060ca01scau.fk474_co0010ca01scaa_clave,
co0060ca01scau.fk639_co0040ca02scaa_clave,
co0060ca01scau.fk333_co0060ca01scaa_id,
co0060ca01scau.fk525_co0030ca02scae_clave
from
co0060ca01scau
inner join
co0060ca01scak on
co0060ca01scak.fk525_co0030ca02scae_clave =
co0060ca01scau.fk525_co0030ca02scae_clave and
co0060ca01scak.fk474_co0010ca01scaa_clave =
co0060ca01scau.fk474_co0010ca01scaa_clave and
co0060ca01scak.fk639_co0040ca02scaa_clave =
co0060ca01scau.fk639_co0040ca02scaa_clave and
co0060ca01scak.fk333_co0060ca01scaa_id =
co0060ca01scau.fk333_co0060ca01scaa_id and
co0060ca01scak.removed = false
inner join
co0060ca01scal on
co0060ca01scal.fk525_co0030ca02scae_clave =
co0060ca01scak.fk525_co0030ca02scae_clave and
co0060ca01scal.fk474_co0010ca01scaa_clave =
co0060ca01scak.fk474_co0010ca01scaa_clave and
co0060ca01scal.fk639_co0040ca02scaa_clave =
co0060ca01scak.fk639_co0040ca02scaa_clave and
co0060ca01scal.removed = false
inner join
co0060ca01scab on
co0060ca01scab.fk474_co0010ca01scaa_clave =
co0060ca01scal.fk474_co0010ca01scaa_clave and
co0060ca01scab.fk639_co0040ca02scaa_clave =
co0060ca01scal.fk639_co0040ca02scaa_clave and
co0060ca01scab.removed = false
inner join
co0030ca02scae on co0030ca02scae.clave =
co0060ca01scal.fk525_co0030ca02scae_clave and co0030ca02scae.removed = false
inner join
co0030ca02scak on co0030ca02scak.clave =
co0030ca02scae.fk550_co0030ca02scak_clave and co0030ca02scak.removed = false
inner join
getperspectivecontent(' || p_perspective_id || ') on
persp_id = co0060ca01scau.perspective_id
where co0060ca01scau.removed = false),
acumulaciones_variables as (select
co0060ca01scad.desde
acumulaciones_variables_desde,
co0060ca01scad.hasta
acumulaciones_variables_hasta,
co0030ca04scae.descripcion
acumulaciones_variables_tipo,
co0060ca01scad.fk165_co0050ca02scaa_clave,
co0060ca01scad.fk474_co0010ca01scaa_clave,
co0060ca01scad.fk639_co0040ca02scaa_clave,
co0060ca01scad.fk333_co0060ca01scaa_id,
co0060ca01scad.fk1338_co0030ca04scad_clave
from
co0060ca01scad
inner join
co0060ca01scac on
co0060ca01scac.fk333_co0060ca01scaa_id = co0060ca01scad.fk333_co0060ca01scaa_id
and
co0060ca01scac.fk1338_co0030ca04scad_clave =
co0060ca01scad.fk1338_co0030ca04scad_clave and
co0060ca01scac.fk474_co0010ca01scaa_clave =
co0060ca01scad.fk474_co0010ca01scaa_clave and
co0060ca01scac.fk639_co0040ca02scaa_clave =
co0060ca01scad.fk639_co0040ca02scaa_clave and
co0060ca01scac.removed = false
inner join
co0060ca01scam on
co0060ca01scam.fk1338_co0030ca04scad_clave =
co0060ca01scac.fk1338_co0030ca04scad_clave and
co0060ca01scam.fk474_co0010ca01scaa_clave =
co0060ca01scac.fk474_co0010ca01scaa_clave and
co0060ca01scam.fk639_co0040ca02scaa_clave =
co0060ca01scac.fk639_co0040ca02scaa_clave and
co0060ca01scam.removed = false
inner join
co0060ca01scab on
co0060ca01scab.fk474_co0010ca01scaa_clave =
co0060ca01scam.fk474_co0010ca01scaa_clave and
co0060ca01scab.fk639_co0040ca02scaa_clave =
co0060ca01scam.fk639_co0040ca02scaa_clave and
co0060ca01scab.removed = false
inner join
co0030ca04scad on co0030ca04scad.clave
= co0060ca01scam.fk1338_co0030ca04scad_clave and co0030ca04scad.removed = false
inner join
co0030ca04scae on co0030ca04scae.clave
= co0030ca04scad.fk1333_co0030ca04scae_clave and co0030ca04scae.removed = false
inner join
getperspectivecontent(' ||
p_perspective_id || ') on persp_id = co0060ca01scad.perspective_id
where co0060ca01scad.removed = false)
select
devengo_basico.devengo_basico_desde,
devengo_basico.devengo_basico_hasta,
devengo_basico.devengo_basico_tipo,
devengo_complementario.devengo_complementario_desde,
devengo_complementario.devengo_complementario_hasta,
devengo_complementario.devengo_complementario_tipo,
tarifas_varias.tarifas_varias_desde,
tarifas_varias.tarifas_varias_hasta,
tarifas_varias.tarifas_varias_tipo,
lsi.lsi_desde,
lsi.lsi_hasta,
lsi.lsi_tipo,
acumulaciones_variables.acumulaciones_variables_desde,
acumulaciones_variables.acumulaciones_variables_hasta,
acumulaciones_variables.acumulaciones_variables_tipo,
co0050ca02scaa.primer_nombre,
co0050ca02scaa.segundo_nombre,
co0050ca02scaa.primer_apellido,
co0050ca02scaa.segundo_apellido,
co0050ca02scaa.clave empelado_clave,
co0040ca02scaa.clave puesto_trabajo_clave,
co0040ca02scaa.descripcion puesto_trabajo_descripcion,
co0010ca01scaa.clave buque_clave,
co0010ca01scaa.descripcion buque_descripcion,
co0060ca01scaa.id jt_id,
co0060ca01scaa.clave jt_clave,
co0060ca01scaa.descripcion jt_descripcion
from co0060ca01scao
inner join
co0060ca01scaa on co0060ca01scaa.id =
co0060ca01scao.fk333_co0060ca01scaa_id and co0060ca01scaa.removed = false
inner join
co0050ca02scaa on co0050ca02scaa.clave =
co0060ca01scao.fk165_co0050ca02scaa_clave and co0050ca02scaa.removed = false
inner join
co0040ca02scaa on co0040ca02scaa.clave =
co0060ca01scao.fk639_co0040ca02scaa_clave and co0040ca02scaa.removed = false
inner join
co0010ca01scaa on co0010ca01scaa.clave =
co0060ca01scaa.fk474_co0010ca01scaa_clave and co0010ca01scaa.removed = false
left join
devengo_basico on co0060ca01scao.fk165_co0050ca02scaa_clave =
devengo_basico.fk165_co0050ca02scaa_clave and
co0060ca01scao.fk333_co0060ca01scaa_id =
devengo_basico.fk333_co0060ca01scaa_id and
co0060ca01scao.fk474_co0010ca01scaa_clave =
devengo_basico.fk474_co0010ca01scaa_clave and
co0060ca01scao.fk639_co0040ca02scaa_clave =
devengo_basico.fk639_co0040ca02scaa_clave
left join
devengo_complementario on co0060ca01scao.fk165_co0050ca02scaa_clave =
devengo_complementario.fk165_co0050ca02scaa_clave and
co0060ca01scao.fk333_co0060ca01scaa_id =
devengo_complementario.fk333_co0060ca01scaa_id and
co0060ca01scao.fk474_co0010ca01scaa_clave =
devengo_complementario.fk474_co0010ca01scaa_clave and
co0060ca01scao.fk639_co0040ca02scaa_clave =
devengo_complementario.fk639_co0040ca02scaa_clave
left join
tarifas_varias on co0060ca01scao.fk165_co0050ca02scaa_clave =
tarifas_varias.fk165_co0050ca02scaa_clave and
co0060ca01scao.fk333_co0060ca01scaa_id =
tarifas_varias.fk333_co0060ca01scaa_id and
co0060ca01scao.fk474_co0010ca01scaa_clave =
tarifas_varias.fk474_co0010ca01scaa_clave and
co0060ca01scao.fk639_co0040ca02scaa_clave =
tarifas_varias.fk639_co0040ca02scaa_clave
left join
lsi on co0060ca01scao.fk165_co0050ca02scaa_clave =
lsi.fk165_co0050ca02scaa_clave and
co0060ca01scao.fk333_co0060ca01scaa_id =
lsi.fk333_co0060ca01scaa_id and
co0060ca01scao.fk474_co0010ca01scaa_clave =
lsi.fk474_co0010ca01scaa_clave and
co0060ca01scao.fk639_co0040ca02scaa_clave =
lsi.fk639_co0040ca02scaa_clave
left join
acumulaciones_variables on co0060ca01scao.fk165_co0050ca02scaa_clave =
acumulaciones_variables.fk165_co0050ca02scaa_clave and
co0060ca01scao.fk333_co0060ca01scaa_id =
acumulaciones_variables.fk333_co0060ca01scaa_id and
co0060ca01scao.fk474_co0010ca01scaa_clave =
acumulaciones_variables.fk474_co0010ca01scaa_clave and
co0060ca01scao.fk639_co0040ca02scaa_clave =
acumulaciones_variables.fk639_co0040ca02scaa_clave
inner join
getperspectivecontent(' || p_perspective_id || ') on persp_id =
co0060ca01scao.perspective_id
where co0060ca01scao.removed = false ';
if p_buque is not null
then
query_str := query_str || ' and co0010ca01scaa.clave = ' ||
p_buque;
end if;
if p_empleado is not null
then
query_str := query_str || ' and co0050ca02scaa.clave = ' ||
p_empleado;
end if;
if p_pt_clave is not null
then
query_str := query_str || ' and co0040ca02scaa.clave = ' ||
p_pt_clave;
end if;
if p_jt_id is not null
then
query_str := query_str || ' and co0060ca01scaa.clave = ' ||
p_jt_id;
end if;
return query execute query_str;
end;
$$
languageplpgsql;
si te das cuenta, tengo una instrucción with en el query que estoy
ejecutando… no había puesto el procedimiento porque la bd con la que
trabaja el sistema tiene estos nombre extraños…
lo que necesito es agarrar os queries que están en el with i no hacer
el left join que se está ejecutando, pues se multiplican los
resultados, necesito agarrar y armar una tabla en la que yo inserte
solamente el resultado de los registros del 1er with, luego del 2do,
luego del 3ro… y asi sucesivamente, obviamente siempre que cumplan con
la condición del with
eso es lo que necesito hacer
saludos
=============================================
"El tamaño de tus logros depende del tamaño de tus metas."
C++ and Qt Senior Developer
Lic. Computer Science
Buenos Aires, Argentina
On May 28, 2015, at 5:17 PM, Gilberto Castillo
<gilberto.casti...@etecsa.cu <mailto:gilberto.casti...@etecsa.cu>> wrote:
si, eso es lo que quiero hacer…. el tema es que aún así, por una demanda
de mi app, necesito organizarla bajo criterios que con un order by no
resuelvo… lo ideal para mi sería hacer los queries correspondiente a
los 5
procesos diferentes en cuestion (porque son 5 queries básicamente
los que
tengo que ejecutar), filtrar la info e ir armando el resulset que quiero
devolver… algo así es lo que quiero hacer…
puedo hacerlo ??
Todo, es posible, pero das muy poca info para inferir otras cosas.
Saludos,
Gilberto Castillo
ETECSA, La Habana, Cuba
---
This message was processed by Kaspersky Mail Gateway 5.6.28/RELEASE
running at host imx3.etecsa.cu
Visit our web-site: <http://www.kaspersky.com>,
<http://www.viruslist.com>
