gistfile1.sql

with TotalSolicitacoes as  (select * from cesuh.tb_solicitacao sol where
((:DE IS NULL) OR 
 (TRUNC(sol.DT_SOLICITACAO) >= TO_DATE(:DE, 'DD/MM/YYYY') AND 
  TRUNC(Sol.DT_SOLICITACAO) <= TO_DATE(:ATE,'DD/MM/YYYY')))
AND ((sol.CD_DIRETORIA = :CD_DIRETORIA) or (:CD_DIRETORIA is null)))
,
TotalAtestadosEnsinoFund as (select Count(1) Qtd from TotalSolicitacoes where cd_tipo_documento = 3 and (CD_TIPO_ENSINO = 1))
,
TotalAtestadoEnsinoMedio as (select Count(1) Qtd from TotalSolicitacoes where cd_tipo_documento = 3 and CD_TIPO_ENSINO = 2)
,
TotalAtestadosEnsinoProf as (select count(1) Qtd from TotalSolicitacoes where cd_tipo_documento = 3 and CD_TIPO_ENSINO = 3)
,
TotalCertificadosEnsinoFund as ( select count(1) Qtd from TotalSolicitacoes where cd_tipo_documento = 1 and (CD_TIPO_ENSINO = 1))
,
TotalCertificadosEnsinoMedio as ( select count(1) Qtd from TotalSolicitacoes where cd_tipo_documento = 1 and (CD_TIPO_ENSINO = 2))
,
TotalCertificadoEnsinoProf as (select Count(1) Qtd from TotalSolicitacoes where cd_tipo_documento = 1 and CD_TIPO_ENSINO = 3)
,
TotalDiplomaEnsinoProf as (select Count(1) Qtd from TotalSolicitacoes where cd_tipo_documento = 2 and CD_TIPO_ENSINO = 3)
,
TotalOutrosNivelFund as (select Count(1) Qtd from TotalSolicitacoes where CD_PROCESSO = 7 and CD_TIPO_ENSINO = 1)
,
TotalOutrosNivelMedio as (select Count(1) Qtd From TotalSolicitacoes where CD_PROCESSO = 7 and CD_TIPO_ENSINO = 2)
,
TotalOutrosNivelProf as (select Count(1) Qtd from TotalSolicitacoes where CD_PROCESSO = 7 and CD_TIPO_ENSINO = 3)
,
TotalOutrosSemTipo as (select Count(1) Qtd from TotalSolicitacoes where CD_PROCESSO = 7 and (CD_TIPO_DOCUMENTO is null and CD_TIPO_ENSINO is null))
,
TotalSolicitacoesConcluida as (select Count(1) Qtd from TotalSolicitacoes where CD_STATUS = 3)
,
TotalSolitacoesAtendimento as (select Count(1) Qtd from TotalSolicitacoes where CD_STATUS = 1)
,
TotalSolicitacoeoRecusadas as (select Count(1) Qtd From TotalSolicitacoes where CD_STATUS = 4)

select A.QTD AS AtestadoEnsinoFund,
      B.QTD AS AtestadoEnsinoProf,
      C.QTD AS AtestadoEnsinMedio,
      D.QTD AS CertificadoEnsinoFund,
      DD.QTD AS CertificadoEnsinoMedio,
      E.QTD AS CertificadoEnsinoProf,
      F.QTD AS DiplomasEnsinoProf,
      G.QTD AS OutrosNivelFund,
      H.QTD AS OutrosNivelMedio,
      I.QTD AS OutroNivelProf,
      J.QTD AS OutrosSemTipo,
      K.QTD AS TotalSolicitacoes,
      L.QTD AS TotalSolicitacoesConcluidas,
      M.QTD AS TotaSolicitacoesEmAtendimento,
      N.QTD AS TotalSolicitacoesRecusdas


from TotalAtestadosEnsinoFund A,
              TotalAtestadosEnsinoProf B,
              TotalAtestadoEnsinoMedio C,
              TotalCertificadosEnsinoFund D,
              TotalCertificadosEnsinoMedio DD,
              TotalCertificadoEnsinoProf E,
              TotalDiplomaEnsinoProf F,
              TotalOutrosNivelFund G,
              TotalOutrosNivelMedio H,
              TotalOutrosNivelProf I,
              TotalOutrosSemTipo J,
              (select Count(1) Qtd from TotalSolicitacoes) K,
              TotalSolicitacoesConcluida L,
              TotalSolitacoesAtendimento M,
              TotalSolicitacoeoRecusadas N
              ;