Dificuldade em executar query começando com With

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Responder
pnet1
Rank: Analista Júnior
Rank: Analista Júnior
Mensagens: 73
Registrado em: Qui, 23 Jan 2020 9:12 pm

Como eu faço para executar essa query?

Selecionar tudo

with VISITA as
 (SELECT id_visita_agendada,
         dt_inativo,
         dt_update,
         hora_inicio,
         hora_fim,
         diasemana,
         codigo_cliente
    FROM trade_visita_agendada UNPIVOT EXCLUDE NULLS
  ( flag_diasemana
      FOR diasemana
       IN ( flag_segunda AS '2',
            flag_terca AS '3',
            flag_quarta AS '4',
            flag_quinta AS '5',
            flag_sexta AS '6',
            flag_sabado AS '7' ) )
   WHERE id_modulo = :ID_MODULO
     AND flag_diasemana = 'S'
     and dt_inativo is null),
PERIODO as
 (select (TO_DATE(:P134_DATAINI) + level - 1) DIASDOANO
    from DUAL
  connect by level <= (TO_DATE(:P134_DATAFIM) - TO_DATE(:P134_DATAINI) + 1)),
USUARIOALTERADO as
 (select USA.CODIGO_CLIENTE, USA.MATRICULA_PROMOTOR, DIAS.DIASDOANO
    from TRADE_ALTERACAOPROMOTOR USA,
         (select (TO_DATE(:P134_DATAINI) + level - 1) DIASDOANO
            from DUAL
          connect by level <=
                     (TO_DATE(:P134_DATAFIM) - TO_DATE(:P134_DATAINI) + 1)) DIAS
   where usa.dt_update <= :P134_DATAFIM
     AND dias.diasdoano <= usa.dt_update
     AND usa.id_modulo = :ID_MODULO
     and not exists
   (select 1
            from TRADE_ALTERACAOPROMOTOR A
           where A.CODIGO_CLIENTE = USA.CODIGO_CLIENTE
             and A.ID_MODULO = USA.ID_MODULO
             and A.DT_UPDATE <= USA.DT_UPDATE
             and A.MATRICULA_PROMOTOR <> USA.MATRICULA_PROMOTOR
             and DIAS.DIASDOANO <= A.DT_UPDATE)),
VISITASREALIZADAS as
 (select  PBR.ID_VISITA_AGENDADA,
                  PBR.CODIGO_CLIENTE,
                  PBR.MATRICULA,
                  USU.NOME PROMOTOR,
                  USU.MATRICULA_SUPERVISOR,
                  USU.SUPERVISOR,
                  USU.MATRICULA_EXECUTIVO,
                  USU.EXECUTIVO,
                  USU.MATRICULA_GERENTE,
                  USU.GERENTE,
                  case when pbr.id_status  =  5 then
                   null
                   else
                  -- Melhoria Checkin e Checkout
                  -- NVL(PBR.HORA_CHECKIN, PBR.HORA_PLANEJADA_CHECKIN)
                   PBR.HORA_CHECKIN
                  end  HORA_CHECKIN,
                  case when pbr.id_status  =  5 then
                   null
                   else
                  -- Melhoria Checkin e Checkout
                  -- NVL(PBR.HORA_CHECKOUT, PBR.HORA_PLANEJADA_CHECKOUT) 
                  PBR.HORA_CHECKOUT
                  end HORA_CHECKOUT,
                  substr(TO_CHAR(PBR.HORA_PLANEJADA_CHECKOUT - PBR.HORA_PLANEJADA_CHECKIN),11,6)  DURACAO_PLANEJADA,
                  substr(TO_CHAR(PBR.HORA_CHECKIN - PBR.HORA_CHECKOUT),11,6)  DURACAO_REALIZADA,
                  PBR.ID_STATUS,
                  case
                    when (NVL(PBR.ID_STATUS, 1) in (0, 1) and
                         TO_DATE(TO_CHAR(PBR.DT_COLETA, 'DD/MM/RRRR ') ||
                                  (TO_CHAR(PBR.HORA_PLANEJADA_CHECKIN,
                                           'HH24:MI:SS')),
                                  'DD/MM/RRRR HH24:MI:SS') > sysdate) then
                     'Não iniciada'
                    when (NVL(PBR.ID_STATUS, 1) in (0, 1) and
                         TRUNC(PBR.DT_COLETA) = TRUNC(sysdate)) then
                     'Não iniciada'
                  
                    when (NVL(PBR.ID_STATUS, 1) in (0, 1,2,3) and
                         TO_DATE(TO_CHAR(PBR.DT_COLETA, 'DD/MM/RRRR ') ||
                                  (TO_CHAR(PBR.HORA_PLANEJADA_CHECKIN,
                                           'HH24:MI:SS')),
                                  'DD/MM/RRRR HH24:MI:SS') < sysdate 
                                and
                         TRUNC(PBR.DT_COLETA) <> TRUNC(sysdate)  ) then
                     'Não realizada'
                                       
                    else
                     NVL((select T.NOMEAPEX
                           from TRADE_STATUS T
                          where T.ID_STATUS = PBR.ID_STATUS),
                         'Não iniciada')
                  end STATUS,
                  case
                    when (NVL(PBR.ID_STATUS, 1) in (0, 1,2,3) and
                         TO_DATE(TO_CHAR(PBR.DT_COLETA, 'DD/MM/RRRR ') ||
                                  (TO_CHAR(PBR.HORA_PLANEJADA_CHECKIN,
                                           'HH24:MI:SS')),
                                  'DD/MM/RRRR HH24:MI:SS') < sysdate 
                                and
                         TRUNC(PBR.DT_COLETA) <> TRUNC(sysdate)  ) then
                     '#FDE0C7'
                  
                    when PBR.ID_STATUS in (2, 3) then
                     '#fef3d2'
                  
                    when PBR.ID_STATUS = 4 then
                     '#e4f0d4'
                  
                    when PBR.ID_STATUS = 5 then
                     '#fcd2c0'
                    else
                     '#EFEFEF'
                  
                  end COR,
                  case
                    when (NVL(PBR.ID_STATUS, 1) in (0, 1,2,3) and
                         TO_DATE(TO_CHAR(PBR.DT_COLETA, 'DD/MM/RRRR ') ||
                                  (TO_CHAR(PBR.HORA_PLANEJADA_CHECKIN,
                                           'HH24:MI:SS')),
                                  'DD/MM/RRRR HH24:MI:SS') < sysdate 
                                and
                         TRUNC(PBR.DT_COLETA) <> TRUNC(sysdate)  ) then
                     'não_realizada'
                  
                    when PBR.ID_STATUS in (2, 3) then
                     'em_andamento'
                  
                    when PBR.ID_STATUS = 4 then
                     'finalizada'
                  
                    when PBR.ID_STATUS = 5 then
                     'cancelada'
                    else
                     'planejada'
                  
                  end ICONE,
                  TRUNC(PBR.DT_COLETA) DT_COLETA,
                  PBR.ID_VISITA,
                  PBR.ID_MOTIVO_CANCELAMENTO,
                  PBR.MATRICULA_CANCELAMENTO AS CANCELADA_POR,
                  null ESPACO
    from TRADE_PUBLICADO_RESULT PBR left join TRADE_VW_USUARIO_SUPERIOR USU on USU.MATRICULA = PBR.MATRICULA-- and PBR.ID_MODULO = USU.ID_MODULO
   where PBR.ID_VISITA_AGENDADA is not null
     and PBR.ID_MODULO = :ID_MODULO
     and to_date(PBR.DT_COLETA,'dd/mm/yy') between TO_DATE(:P134_DATAINI,'dd/mm/yy') and TO_DATE(:P134_DATAFIM,'dd/mm/yy')
     and PBR.CODIGO_CLIENTE = NVL(:P134_CODIGO_CLIENTE, PBR.CODIGO_CLIENTE)
     and ((USU.MATRICULA = NVL(:P134_MATRICULA, :P134_MATRICULAUSER)) or
         (USU.MATRICULA_SUPERVISOR =
         NVL(:P134_MATRICULA, :P134_MATRICULAUSER)) or
         (USU.MATRICULA_EXECUTIVO =
         NVL(:P134_MATRICULA, :P134_MATRICULAUSER)) or
         (USU.MATRICULA_GERENTE = NVL(:P134_MATRICULA, :P134_MATRICULAUSER)) or
         (USU.MATRICULA_DIRETOR = NVL(:P134_MATRICULA, :P134_MATRICULAUSER)) or
         /*exists (select 1
                   from TRADE_USUARIOMASTER M
                  where M.MATRICULA = :P134_MATRICULAUSER
                    and :P134_MATRICULA is null)))*/
         (:PERFILACESSO = 'ADMIN' and :P134_MATRICULA is null)))
select rownum, X.*,'<div class="'||X.ICONE||'" style="display: flex; align-items: center;">' ||
'<img class="status-img" style="max-width: 20px!important" src="&APP_IMAGES.ico_status_visita_'||X.ICONE||'@3x.png">' ||
'<span style="padding-left: 5px; display: inline-block;">'||X.STATUS||'</span>' ||
'<div>'  STATUS_HTML ,
     (select (select flt.nome_tipo
          from trade_filtro flt
         where flt.id_filtro = flc.id_filtro
           and flt.tipo_filtro = flc.tipo_filtro)
  from trade_filtro_cliente flc
  where flc.tipo_filtro = 'REGIONAL'
    and flc.id_modulo = :ID_MODULO
    and  FLC.CODIGO_CLIENTE = X.CODIGO_CLIENTE) REGIONAL from (                    
--Visitas agendas não gravadas na result considerando promotor na tabela de cliente e promotor alterado
select VIS.ID_VISITA_AGENDADA,
       T.DIASDOANO,
       TO_CHAR(T.DIASDOANO, 'Day', 'NLS_DATE_LANGUAGE=PORTUGUESE') SEMANA,
       VIS.CODIGO_CLIENTE,
       USU.MATRICULA,
       USU.NOME PROMOTOR,
       USU.MATRICULA_SUPERVISOR,
       USU.SUPERVISOR,
       USU.MATRICULA_EXECUTIVO,
       USU.EXECUTIVO,
       USU.MATRICULA_GERENTE,
       USU.GERENTE,
       /*
         Melhoria Checkin e Checkout
         
         TO_CHAR(TO_DATE(DECODE(HORA_INICIO,
                              null,
                              null,
                              TO_CHAR(T.DIASDOANO, 'DD/MM/YYYY ') ||
                              TO_CHAR(VIS.HORA_INICIO, 'HH24:MI:SS')),
                       'DD/MM/YYYY HH24:MI:SS'),
               'HH24:MI:SS') INICIO,
       TO_CHAR(TO_DATE(DECODE(HORA_FIM,
                              null,
                              null,
                              TO_CHAR(T.DIASDOANO, 'DD/MM/YYYY ') ||
                              TO_CHAR(VIS.HORA_FIM, 'HH24:MI:SS')),
                       'DD/MM/YYYY HH24:MI:SS'),
               'HH24:MI:SS') FIM,*/
       null INICIO,
       null FIM,
       substr(TO_CHAR(VIS.HORA_FIM - VIS.HORA_INICIO),11,6)  DURACAO_PLANEJADA,
       null  DURACAO_REALIZADA,
       case
         when TO_DATE(TO_CHAR(T.DIASDOANO, 'DD/MM/RRRR ') ||
                      (TO_CHAR(VIS.HORA_INICIO, 'HH24:MI:SS')),
                      'DD/MM/RRRR HH24:MI:SS') > sysdate then
          'Não iniciada'
         when TRUNC(T.DIASDOANO) = TRUNC(sysdate) then
          'Não iniciada'
       
         else
          'Não realizada'
       end STATUS,
       (case
         /*when TO_DATE(TO_CHAR(T.DIASDOANO, 'DD/MM/RRRR ') ||
                      (TO_CHAR(VIS.HORA_INICIO, 'HH24:MI:SS')),
                      'DD/MM/RRRR HH24:MI:SS') > sysdate then*/
         when trunc(T.DIASDOANO) >= trunc(sysdate) then
          '#EFEFEF'
         else
          '#FDE0C7'
       end) COR,
       case
         /*when TO_DATE(TO_CHAR(T.DIASDOANO, 'DD/MM/RRRR ') ||
                      (TO_CHAR(VIS.HORA_INICIO, 'HH24:MI:SS')),
                      'DD/MM/RRRR HH24:MI:SS') > sysdate then*/
          when trunc(T.DIASDOANO) >= trunc(sysdate) then
          'planejada'
         else
          'não_realizada'
       end ICONE,
       null ID_VISITA,
       (select APEX_UTIL.PREPARE_URL('f?p=&APP_ID.:115:&APP_SESSION.::NO:RP:' ||
                                     'P115_DATA,P115_CODIGO_CLIENTE,P115_MATRICULA,P115_HORA_INICIO,P115_HORA_FIM,P115_STATUS,P115_ID_VISITA_AGENDADA:' ||
                                     
                                     TO_CHAR(T.DIASDOANO, 'DD-MM-RRRR') || ',' ||
                                     VIS.CODIGO_CLIENTE || ',' ||
                                     USU.MATRICULA || ',' ||
                                     TO_CHAR(VIS.HORA_INICIO, 'HH24-MI-SS') || ',' ||
                                     TO_CHAR(VIS.HORA_FIM, 'HH24-MI-SS') || ',' ||
                                     (case
                                       when TO_DATE(TO_CHAR(T.DIASDOANO, 'DD/MM/RRRR ') ||
                                                    (TO_CHAR(VIS.HORA_INICIO, 'HH24:MI:SS')),
                                                    'DD/MM/RRRR HH24:MI:SS') > sysdate then
                                        'Não iniciada'
                                       when TRUNC(T.DIASDOANO) = TRUNC(sysdate) then
                                        'Não iniciada'
                                     
                                       else
                                        'Não realizada'
                                     end) || ',' || VIS.ID_VISITA_AGENDADA)
        
          from DUAL) URL,
       CAST(NULL AS NUMBER) ID_MOTIVO_CANCELAMENTO,
       NULL CANCELADA_POR,
        null ESPACO
  from PERIODO T
 inner join VISITA VIS
    on VIS.DIASEMANA = TO_CHAR(T.DIASDOANO, 'd')
 inner join TRADE_VW_CLIENTE CLI
    on CLI.CODIGO_CLIENTE = VIS.CODIGO_CLIENTE
   and CLI.ID_MODULO = :ID_MODULO
   AND cli.ativo = 'S'
  left join USUARIOALTERADO USA
    on USA.CODIGO_CLIENTE = VIS.CODIGO_CLIENTE
   and USA.DIASDOANO = T.DIASDOANO
 inner join TRADE_VW_USUARIO_SUPERIOR USU
    on NVL(USA.MATRICULA_PROMOTOR, CLI.MATRICULA_PROMOTOR) = USU.MATRICULA
   and USU.ID_MODULO = CLI.ID_MODULO  
   AND USU.ativo = 'S'
 where T.DIASDOANO <= NVL(TRUNC(VIS.DT_INATIVO), :P134_DATAFIM) 
        AND CLI.ID_MODULO = :ID_MODULO
        AND VIS.ID_VISITA_AGENDADA IN(SELECT ID_VISITA_AGENDADA FROM TRADE_VISITA_AGENDADA
                          WHERE TRUNC(VIGENCIA) =
                          (select max(trunc(vigencia))from trade_visita_agendada where vigencia is not null and trunc(vigencia)<=trunc(t.diasdoano) and id_modulo = :ID_MODULO))

        
         and vis.dt_inativo is null
         AND CLI.ATIVO = 'S'
         AND CLI.MATRICULA_PROMOTOR IN(SELECT MATRICULA FROM TRADE_USUARIO WHERE ATIVO = 'S')
   and T.DIASDOANO >= TRUNC(VIS.DT_UPDATE)
   and VIS.CODIGO_CLIENTE = NVL(:P134_CODIGO_CLIENTE, VIS.CODIGO_CLIENTE)
   and ((USU.MATRICULA = NVL(:P134_MATRICULA, :P134_MATRICULAUSER)) or
       (USU.MATRICULA_SUPERVISOR =
       NVL(:P134_MATRICULA, :P134_MATRICULAUSER)) or
       (USU.MATRICULA_EXECUTIVO =
       NVL(:P134_MATRICULA, :P134_MATRICULAUSER)) or
       (USU.MATRICULA_GERENTE = NVL(:P134_MATRICULA, :P134_MATRICULAUSER)) or
       (USU.MATRICULA_DIRETOR = NVL(:P134_MATRICULA, :P134_MATRICULAUSER)) or
       /*exists (select 1
                 from TRADE_USUARIOMASTER M
                where M.MATRICULA = :P134_MATRICULAUSER
                  and :P134_MATRICULA is null))*/
       (:PERFILACESSO = 'ADMIN' and :P134_MATRICULA is null))
   and not exists (select 1
          from VISITASREALIZADAS VR
         where TRUNC(VR.DT_COLETA) = TRUNC(T.DIASDOANO)
           and VR.MATRICULA = USU.MATRICULA
           and VR.CODIGO_CLIENTE = VIS.CODIGO_CLIENTE)
/*   and not exists
 (select 1
          from TRADE_PUBLICADO_RESULT R
         where FUN_SEMANA_ANO(R.DT_COLETA) = FUN_SEMANA_ANO(T.DIASDOANO)
           and VIS.ID_VISITA_AGENDADA = R.ID_VISITA_AGENDADA
           and R.MATRICULA = USU.MATRICULA
           and VIS.FLAG_VISITA_UNICA = 'S')*/
   and to_date(T.DIASDOANO,'dd/mm/yy') between TO_DATE(:P134_DATAINI,'DD/MM/YY') and TO_DATE(:P134_DATAFIM,'DD/MM/YY')
       --AND  T.DIASDOANO between TRUNC(:P134_DATAINI) AND TRUNC(:P134_DATAFIM)
       --and to_date(:P134_DATAFIM,'dd/mm/yy') >= to_date(sysdate,'dd/mm/yy')
        AND to_date(T.DIASDOANO,'dd/mm/yy') >= TO_DATE(SYSDATE,'DD/MM/YY')
union all

select VR.ID_VISITA_AGENDADA,     
       VR.DT_COLETA DIASDOANO,
       TO_CHAR(VR.DT_COLETA, 'Day', 'NLS_DATE_LANGUAGE=PORTUGUESE') SEMANA,
       VR.CODIGO_CLIENTE,
       VR.MATRICULA,
       VR.PROMOTOR,
       VR.MATRICULA_SUPERVISOR,
       VR.SUPERVISOR,
       VR.MATRICULA_EXECUTIVO,
       VR.EXECUTIVO,
       VR.MATRICULA_GERENTE,
       VR.GERENTE,
       TO_CHAR(VR.HORA_CHECKIN, 'HH24:MI:SS') INICIO,
       TO_CHAR(VR.HORA_CHECKOUT, 'HH24:MI:SS') FIM,
       VR.DURACAO_PLANEJADA,
       VR.DURACAO_REALIZADA,
       VR.STATUS,
       COR,
       ICONE,
       VR.ID_VISITA,
       (select APEX_UTIL.PREPARE_URL('f?p=&APP_ID.:119:&APP_SESSION.::NO:RP:P119_DATA,P119_CODIGO_CLIENTE,P119_MATRICULA,P119_STATUS,P119_ID_VISITA,P119_ID_VISITA_AGENDADA:' ||
                                     TO_CHAR(VR.DT_COLETA, 'DD-MM-RRRR') || ',' ||
                                     VR.CODIGO_CLIENTE || ',' ||
                                     VR.MATRICULA || ',' || VR.STATUS || ',' ||
                                     VR.ID_VISITA || ',' ||
                                     ID_VISITA_AGENDADA)
        
          from DUAL) URL,
       VR.ID_MOTIVO_CANCELAMENTO,
       VR.CANCELADA_POR,
        null ESPACO
  from VISITASREALIZADAS VR /*WHERE VR.ID_VISITA_AGENDADA IN(SELECT ID_VISITA_AGENDADA FROM TRADE_VISITA_AGENDADA WHERE DT_INATIVO IS NULL)*/) X
Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5024
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Com a cláusula WITH você pode criar uma "pseudo" tabela e dar a ela um nome.
Algo assim:

Selecionar tudo

with MINHA_TABELA as
 (SELECT 'Test' FROM dual )
select * from MINHA_TABELA;
Isso é util quando você precisa utilizar um resultado em várias outras queries.
Daí em vez de repetir a sub-query várias vezes, você simplesmente coloca ela no WITH e usa quantas vezes quiser.
pnet1
Rank: Analista Júnior
Rank: Analista Júnior
Mensagens: 73
Registrado em: Qui, 23 Jan 2020 9:12 pm

Entendi e resolvido. Eu consegui executar sim. Só preciso entender em como reutilizar as "pseudo tabela".
Avatar do usuário
tiago_pimenta
Rank: DBA Júnior
Rank: DBA Júnior
Mensagens: 213
Registrado em: Qua, 29 Jun 2011 9:49 am
Localização: Barretos / SP

pnet1 escreveu:
Ter, 28 Dez 2021 4:40 pm
Entendi e resolvido. Eu consegui executar sim. Só preciso entender em como reutilizar as "pseudo tabela".
Imagina o select abaixo:

Selecionar tudo

select * 
from tabela a,
     (select 1
     from dual) b

union

select * 
from tabela c,
     (select 1
     from dual) b
A parte do sub-select, que é repetido duas vezes, você poderia criar um with e usar o "alias" dele em vez de repetir o sub-select... Entendeu ???
Responder
  • Informação