|
-
basically I am trying to write the two queries in from clause (inline view X & Y) into one, most probably it´s possible since the conditions are the same however I find it only possible with outer joins and a couple of analytic functions but with outer joins the performance would suffer in this case
May be someone else know a way without using outer joins?
Code:
select sum(case when x.estado = 'Error' and x.accion = 'Crear Intercambio' and x.abierto = 0 and x.id_cau is null
then 1
else 0
end) MAL_ABIERTO,
sum(case when x.estado = 'Exito' and x.accion = 'Crear Intercambio' and x.abierto = 0 and x.id_cau is not null
then 1
else 0
end) - sum(case when x.estado = 'Exito'and x.accion = 'Cerrar Intercambio'and x.abierto = 1
then 1
else 0
end) ABIERTO,
sum(case when x.estado = 'Exito' and x.accion = 'Cerrar Intercambio' and x.abierto = 1
then 1
else 0
end) CERRADO,
sum(case when x.estado = 'Error' and x.accion = 'Cerrar Intercambio' and x.abierto = 1
then 1
else 0
end) MAL_CERRADO,
sum(case when x.accion = 'Crear Intercambio' and x.abierto = 1
then 1
else 0
end) - sum(case when x.estado = 'Exito' and x.accion = 'Cerrar Intercambio' and x.abierto = 1
then 1
else 0
end) PENDIENTES,
SIN_INTERCAMBIO,
TOTAL
from (select i.x_intercambio2case objid_case,max(i.objid),z.id_number,i.x_id_number id_cau,
a.x_accion accion,a.x_estado estado,a.x_act2intercambio objid_intercambio,
a.x_usuario,i.x_abierto_cerrado abierto, numero total
from table_x_act_log a,table_x_intercambio i,
(select k.objid, k.id_number, max(numero) over () numero
from (select c.objid, c.id_number, row_number() over (order by c.objid,c.id_number desc) numero
from table_case c, table_queue q
where c.case_currq2queue = q.objid
and q.objid='268436260') K) Z
where a.x_act2intercambio = i.objid
and i.x_intercambio2case = z.objid
group by i.x_intercambio2case,z.id_number,i.x_id_number,a.x_accion,a.x_estado,
a.x_act2intercambio,a.x_usuario,i.x_abierto_cerrado, numero) X,
(select count(*) SIN_INTERCAMBIO
from table_case c,table_queue q
where c.case_currq2queue = q.objid
and q.objid = '268436260'
and not exists (select null
from table_x_intercambio i
where c.objid = i.x_intercambio2case)) Y
group by SIN_INTERCAMBIO, TOTAL
;
-
ahh I made it 
these dwh functions are lovely
Code:
SELECT SUM(CASE WHEN X.ESTADO = 'Exito' AND X.ACCION = 'Crear Intercambio' AND X.ABIERTO = 0 AND X.ID_CAU IS NOT NULL
THEN 1
ELSE 0
END) ABIERTO,
SUM(CASE WHEN X.ESTADO = 'Error' AND X.ACCION = 'Crear Intercambio' AND X.ABIERTO = 0 AND X.ID_CAU IS NULL
THEN 1
ELSE 0
END) MAL_ABIERTO,
SUM(CASE WHEN X.ESTADO = 'Exito' AND X.ACCION = 'Cerrar Intercambio' AND X.ABIERTO = 1
THEN 1
ELSE 0
END) CERRADO,
SUM(CASE WHEN X.ESTADO = 'Error' AND X.ACCION = 'Cerrar Intercambio' AND X.ABIERTO = 1
THEN 1
ELSE 0
END) MAL_CERRADO,
SUM(CASE WHEN X.ESTADO = 'Exito' AND X.ACCION = 'Crear Intercambio' AND X.ABIERTO = 1 AND X.ID_CAU IS NULL
THEN 1
ELSE 0
END) CERRADO_MANUAL,
SUM(CASE WHEN X.ESTADO = 'Exito' AND X.ACCION = 'Crear Intercambio' AND X.ABIERTO = 1 AND X.ID_CAU IS NOT NULL
THEN 1
ELSE 0
END) DEVOLVER,
SUM(CASE WHEN X.ESTADO = 'Pendiente'
THEN 1
ELSE 0
END) PENDIENTES,
SUM(CASE WHEN X.OBJID_CASE IS NULL
THEN X.SIN_INTERCAMBIO
ELSE 0
END) SIN_INTERCAMBIO,
MAX(TOTAL) TOTAL
FROM (SELECT
T.OBJID_CASE,T.IOBJID,T.ID_NUMBER,T.ID_CAU,T.ACCION,T.ESTADO,T.OBJID_INTERCAMBIO,
T.X_USUARIO,T.ABIERTO,T.TOTAL,T.SIN_INTERCAMBIO
FROM (SELECT R.OBJID_CASE,R.IOBJID,R.ID_NUMBER,R.ID_CAU,R.ACCION,R.ESTADO,R.OBJID_INTERCAMBIO,
R.X_USUARIO,R.ABIERTO,R.TOTAL,R.SIN_INTERCAMBIO,
ROW_NUMBER () OVER (PARTITION BY R.OBJID_CASE ORDER BY R.IOBJID DESC) NUM1
FROM (SELECT Z.X_INTERCAMBIO2CASE OBJID_CASE,MAX(Z.IOBJID) IOBJID,Z.ID_NUMBER,Z.X_ID_NUMBER ID_CAU,
A.X_ACCION ACCION,A.X_ESTADO ESTADO,A.X_ACT2INTERCAMBIO OBJID_INTERCAMBIO,
A.X_USUARIO,Z.X_ABIERTO_CERRADO ABIERTO,NUMERO TOTAL,
COUNT(Z.SIN_INTERCAMBIO) OVER (PARTITION BY Z.SIN_INTERCAMBIO) SIN_INTERCAMBIO
FROM TABLE_X_ACT_LOG A,
(SELECT V.OBJID, V.ID_NUMBER, V.NUMERO,I.X_INTERCAMBIO2CASE,I.OBJID IOBJID,
I.X_ID_NUMBER,I.X_ABIERTO_CERRADO, NVL(I.X_INTERCAMBIO2CASE,-1) SIN_INTERCAMBIO
FROM TABLE_X_INTERCAMBIO I,
(SELECT K.OBJID, K.ID_NUMBER, MAX(NUMERO) OVER () NUMERO
FROM (SELECT C.OBJID,C.ID_NUMBER,
ROW_NUMBER() OVER (ORDER BY C.OBJID,C.ID_NUMBER DESC) NUMERO
FROM TABLE_CASE C, TABLE_QUEUE Q
WHERE C.CASE_CURRQ2QUEUE = Q.OBJID
AND Q.OBJID = '268436260') K) V
WHERE I.X_INTERCAMBIO2CASE (+) = V.OBJID) Z
WHERE A.X_ACT2INTERCAMBIO(+) = Z.IOBJID
GROUP BY Z.X_INTERCAMBIO2CASE,Z.ID_NUMBER,Z.X_ID_NUMBER,A.X_ACCION,A.X_ESTADO,
A.X_ACT2INTERCAMBIO,A.X_USUARIO,Z.X_ABIERTO_CERRADO,NUMERO,Z.SIN_INTERCAMBIO) R
WHERE R.ESTADO IN ('Error','Exito','Pendiente')
AND R.ACCION IN ('Crear Intercambio', 'Cerrar Intercambio')
OR R.ESTADO IS NULL
AND R.ACCION IS NULL) T
WHERE T.NUM1 = 1) X;
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|
Reply With Quote