Count qtde of records in the same select with different conditions

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Post Reply
Tinho
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 325
Joined: Mon, 16 Nov 2009 4:50 pm
Location: São Paulo - SP

Galera, I believe you have another way of doing something like I did below:

Select all

 
 
select  A16.* 
       ,CASE WHEN (      A16.AUTORIZA IS NULL  
                    AND ( SELECT COUNT(*) 
					        FROM OWNER.TABELA  
						   WHERE CAMPO1    = 1  
						     AND CAMPO2 = '2171200006643'  
							 AND NUM_RIESGO = 470 ) <>  ( SELECT COUNT(*)  
							                                FROM OWNER.TABELA 
														   WHERE CAMPO1 = 1  
														     AND CAMPO2 = '2171200006643'  
															 AND RISCO = 470  
															 AND AUTORIZA = 'S' )  
                   ) THEN 'FALHA NA APROVAÇÃO' 
                     ELSE 'APROVADO'             
        END ANALISE 
  from OWNER.TABELA A16 
 where A16.CAMPO1    = 1  
   and A16.CAMPO2 = '2171200006643' 
   AND A16.RISCO  = 470 
 
In this case, it is a parameterization table that indicates If a certain process has been successfully completed or not. If it has been successfully completed the authoriza field is filled and what I intended with this script was to verify that the amount of records for a particular key is the same amount for the same records that have said filled field, because all field are listed independent of having been filled or not. Hence I pick up and compare the two quantities if they are equal that this indicates that all records have been approved or authorized.

Well, maybe someone asks: but why do not you just bring the fields filled then?

Why do I need to know the fields that have not been filled as well because an error code is generated. I'm just trying to make it easier since we have to analyze a sequence of several tables and some specific flags.

I do not know if it was clear, could you understand? Will the cube or the rollup help in this case?

If someone has any suggestions, I'm waiting.

Thanks.

Att.,
User avatar
stcoutinho
Moderador
Moderador
Posts: 850
Joined: Wed, 11 May 2011 5:15 pm
Location: são Paulo - SP

Hi Tinho,

I can not evaluate if my queries are correct 100%, but you can test one of the options below:

Select all

  SELECT COUNT(*) AS TOTAL, 
         SUM(CASE WHEN AUTORIZA = 'S' THEN 1 ELSE 0 END) AS AUTORIZADO   
    FROM OWNER.TABELA 
   WHERE CAMPO1 = 1  
     AND CAMPO2 = '2171200006643'  
     AND RISCO = 470

Select all

SELECT CASE (COUNT(*) - SUM(CASE WHEN AUTORIZA = 'S' THEN 1 ELSE 0 END)) <> 0 THEN 'FALHA NA APROVACAO' ELSE 'APROVADO' END AS STATUS 
    FROM OWNER.TABELA 
   WHERE CAMPO1 = 1  
     AND CAMPO2 = '2171200006643'  
     AND RISCO = 470

Select all

  SELECT CAMPO1,CAMPO2,RISCO, 
      COUNT(*) AS TOTAL, 
     SUM(CASE WHEN AUTORIZA = 'S' THEN 1 ELSE 0 END) AS AUTORIZADO   
        FROM OWNER.TABELA 
      GROUP BY CAMPO1,CAMPO2,RISCO

Select all

  SELECT CAMPO1,CAMPO2,RISCO, 
      CASE (COUNT(*) - SUM(CASE WHEN AUTORIZA = 'S' THEN 1 ELSE 0 END)) <> 0 THEN 'FALHA NA APROVACAO' ELSE 'APROVADO' END AS STATUS 
        FROM OWNER.TABELA 
      GROUP BY CAMPO1,CAMPO2,RISCO
I do not know if you really need to use rollup or cube. But it may be that other forists have a different opinion.

Hugs,

Sergio Coutinho
Post Reply
  • Information
  • Who is online

    Users browsing this forum: Bing [Bot] and 1 guest