Re: acerac de union

una parte de la funcion es esta: (CON UNA EXPLICACION EN ESTA PARTE PODRIA
CONTROLAR EL RESTO, O MEJOR SERIA CREAR CURSORES ?)

---EGRESOSS
SELECT '' AS idccodpl, det.steccoen AS idccodso,
to_date(eg.stedfech, 'YYYY/MM/DD'::text) AS
sccdfech, 'EGR' AS scctipo,
SUM(CASE WHEN (det.idccodcu ~~ '5011%'::text) THEN
CASE WHEN det.stenafec = 1 THEN (0-det.stenmont) ELSE
det.stenmont END
ELSE 0.00 END) AS sccoapor,

SUM(CASE WHEN ((det.idccodcu ~~ '1411%'::text) OR (det.idccodcu ~~
'1412%'::text) OR (det.idccodcu ~~ '166201'::text) OR (det.idccodcu ~~
'141501'::text)) THEN
CASE WHEN det.stenafec != 1 THEN (0-det.stenmont) ELSE
det.stenmont END
ELSE 0::numeric END) AS sccopram,

SUM(CASE WHEN (det.idccodcu ~~ '7331%'::text) THEN
CASE WHEN det.stenafec != 1 THEN (0-det.stenmont) ELSE
det.stenmont END
ELSE 0::numeric END) AS sccoprin,

SUM(CASE WHEN (det.idccodcu ~~ '743101%'::text) THEN
CASE WHEN det.stenafec != 1 THEN (0-det.stenmont) ELSE
det.stenmont END
ELSE 0::numeric END) AS sccoprmo,

SUM(CASE WHEN (((((det.idccodcu ~~ '1661%'::text) OR
(det.idccodcu~~ '7551%'::text)) AND (
det.idccodcu <>
'166106'::character varying)) AND (det.idccodcu <>
'166107'::character varying)) AND
(det.idccodcu <> '166108'::character varying) AND (det.idccodcu <>
'166111'::character varying)) THEN
det.stenmont ELSE 0::numeric END) AS sccoacti,

SUM(CASE WHEN ((det.idccodcu = '166106'::character varying) OR (
det.idccodcu = '751101'::character varying)) THEN
CASE WHEN det.stenafec = 1 THEN (0-det.stenmont) ELSE
det.stenmont END
ELSE 0::numeric END) AS sccomant,

SUM(CASE WHEN ((det.idccodcu = '166107'::character varying) OR (
det.idccodcu = '757101'::character varying)) THEN
CASE WHEN det.stenafec = 1 THEN (0-det.stenmont) ELSE
det.stenmont END
ELSE 0::numeric END) AS sccofose,

SUM(CASE WHEN ((det.idccodcu = '166108'::character varying) OR (
det.idccodcu = '166111'::character varying) OR (det.idccodcu =
'754101'::character varying)) THEN
det.stenmont ELSE 0::numeric END) AS sccomult,

det.idcegres AS idcodocu, det.stecsueg AS idcsucur,

SUM(CASE WHEN (det.idccodcu = '141401'::character varying) THEN
(0-det.stenmont) ELSE 0::numeric END) AS sccocred,

SUM(CASE WHEN (((det.idccodcu = '465102'::character varying) OR (
det.idccodcu = '465101'::character varying)) OR
(det.idccodcu = '464101'::character varying)) THEN
(0-det.stenmont) ELSE 0::numeric END) AS sccootro,

'' AS sccoefco, '' AS scoperio

FROM stecabeg eg, stedeteg det
WHERE (eg.idcegres = det.idcegres) AND (eg.stecsueg = det.stecsueg) AND (
eg.stectien = det.stectien) AND
eg.stectien = 'SOCIO'::character varying AND det.stectien =
'SOCIO'::character varying AND
det.steccoen = '000000000014992' AND eg.anulado = 0::numeric AND
eg.eliminar = 0::numeric
GROUP BY det.steccoen, eg.stedfech, det.idcegres, det.stecsueg

*UNION ALL*

----INGRESOS
SELECT '' AS idccodpl, det.steccoen AS idccodso,
to_date(ing.stedfech, 'YYYY/MM/DD'::text) AS
sccdfech, 'ING' AS scctipo,

SUM(CASE WHEN (det.idccodcu ~~ '5011%'::text) THEN det.stenmont ELSE
0.00 END) AS sccoapor,

SUM(CASE WHEN (((det.idccodcu ~~ '1411%'::text) OR (det.idccodcu ~~
'1412%'::text) OR (det.idccodcu ~~ '1415%'::text))) THEN
(0-det.stenmont) ELSE 0::numeric END) AS sccopram,

SUM(CASE WHEN (det.idccodcu ~~ '7331%'::text) THEN
CASE WHEN det.stenafec != 1 THEN (0-det.stenmont) ELSE
det.stenmont END
ELSE 0::numeric END) AS sccoprin,

SUM(CASE WHEN (det.idccodcu ~~ '743101%'::text) THEN
CASE WHEN det.stenafec != 1 THEN (0-det.stenmont) ELSE
det.stenmont END
ELSE 0::numeric END) AS sccoprmo,

SUM(CASE WHEN (((det.idccodcu IN (SELECT ta.idccodcu FROM scrtipac
ta WHERE idtipact NOT IN ('00006','00013'))) OR (det.idccodcu ~~
'7551%'::text)) AND (det.idccodcu NOT IN ('755106'))) THEN
CASE WHEN det.stenafec != 1 THEN (0-det.stenmont) ELSE
det.stenmont END
ELSE 0::numeric END) AS sccoacti,

SUM(CASE WHEN ((det.idccodcu = '166106'::character varying) OR (
det.idccodcu =
'751101'::character varying)) THEN
CASE WHEN det.stenafec = 1 THEN (0-det.stenmont) ELSE
det.stenmont END
ELSE 0::numeric END) AS sccomant,

SUM(CASE WHEN ((det.idccodcu = '166107'::character varying) OR (
det.idccodcu = '757101'::character varying)) THEN
det.stenmont ELSE 0::numeric END) AS sccofose,

SUM(CASE WHEN ((det.idccodcu = '166108'::character varying) OR (
det.idccodcu = '754101'::character varying) OR (det.idccodcu =
'166111'::character varying)) THEN
det.stenmont ELSE 0::numeric END) AS sccomult,

det.idcingre AS idcodocu, ing.idcsucur,

SUM(CASE WHEN ((det.idccodcu ~~ '1413%'::text) OR (det.idccodcu ~~
'1414%'::text)) THEN
(0-det.stenmont) ELSE 0::numeric END) AS sccocred,

SUM(CASE WHEN (((det.idccodcu = '465102'::character varying) OR (
det.idccodcu =
'465101'::character varying)) OR (det.idccodcu = '464101'::character
varying) OR (det.idccodcu ~~ '751201%'::text)) THEN
det.stenmont ELSE 0::numeric END) AS sccootro,
'' AS sccoefco, '' AS scoperio
FROM stecabin ing, stedetin det
WHERE (ing.idcingre = det.idcingre) AND (ing.stectien = det.stectien) AND (
ing.idcsucur = det.idcsucur) AND
ing.stectien = 'SOCIO'::character varying AND det.stectien =
'SOCIO'::character varying AND
det.steccoen = '000000000014992' AND ing.eliminar = 0::numeric AND
ing.anulado = 0::numeric
GROUP BY det.steccoen, ing.stedfech, det.idcingre, ing.idcsucur

On 3/15/07, Alvaro Herrera <alvherre(at)commandprompt(dot)com> wrote:
>
> JOSE CODARLUPO escribió:
> > Hola amigos:
> > Tengo una preocupacion con el uso del comano UNION ALL dentro de una
> funcion
> > se hace uso dentro de esta funcion en 8 veces para difrentes SELECT.
> Existe
> > alguna forma de optimizar el uso de esta sentencia.
>
> Una idea es eliminar 7 diferentes SELECT y dejar solo uno, y con eso
> eliminas y optimizas totalmente el uso de UNION ALL.
>
> Otra idea seria que nos mostraras un ejemplo concreto a ver si podemos
> darte ayuda inteligente.
>
> --
> Alvaro Herrera
> http://www.CommandPrompt.com/
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>