merge SQL stmt
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: merge SQL stmt

  1. #1
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    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
    ;

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    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
  •  



Click Here to Expand Forum to Full Width