mruether's questions

O problema descrito a seguir se aplica aos bancos de dados Oracle 11g. Também testei as instruções no MySQL e PostgreSQL sem problemas do tipo mencionado nesta pergunta.

Eu quero usar uma função de agregação em uma instrução de seleção que precisa de algum cálculo adicional para produzir o resultado. Meu objetivo principal é manter todo o cálculo em uma declaração, pois o resultado deve ser exibido literalmente no software de back-end.

Que haja uma tabela Tdefinida por este DDL

create table T(
  ID number,
  NUM number,
  TXT varchar(32)
);

insert into T (ID, NUM, TXT) values (1, 1, 'Text1');
insert into T (ID, NUM, TXT) values (2, 2, 'Text2');
insert into T (ID, NUM, TXT) values (3, 0, 'Text3');

Agora o objetivo é somar todos os valores do atributo num para todas as linhas selecionadas. Eu posso fazer isso usando

select sum(num) from T

Agora, para a parte complicada, suponha que haja uma segunda tabela a partir da qual calculamos um valor adicional a ser combinado com o resultado de nossa primeira seleção. O select envolvido retornará um único número por ser um valor agregado por si só. (Para manter o esquema simples desta demonstração, utilizo a mesma tabela, mas o problema descrito permanece independentemente da segunda tabela utilizada)

select case (select count(*) from T) when 0 then 1 else 0 end from dual;

Quando combinados, deixaremos de fora a select ... from dualparte que é usada apenas para obter o resultado intermediário exibido. Juntos, a consulta será

select (case (select count(t1.num) from T t1) when 0 then 1 else 0 end)*sum(t2.num) from T t2;

onde atribuímos aliases a cada tabela para tornar o uso claro.

O resultado dessa consulta é uma exceção do Oracle: ORA-00937: not a single-group group function. Como a expressão case produz um único valor e nenhuma referência é feita a nenhum recurso fora da expressão, eu me pergunto qual princípio básico eu violei com essa instrução select. Além disso, MySQL e PostgreSQL produzem o resultado desejado.

Enquanto procurava uma solução, também tentei esta instrução select:

select (case when (exists (select 1 from T where NUM = 0)) then 0 else 1 end)*sum(NUM) as p from T where NUM > 0;

Aqui descartei a função de agregação interna count(t1.num)para descartar problemas com agregações aninhadas. Esta instrução deu a mesma mensagem de erro do Oracle, enquanto o cálculo do valor adicional não causou problemas:

select (case when (exists (select 1 from T where NUM = 0)) then 0 else 1 end) as p from dual;

Pergunta : Alguém pode explicar o que está errado? (E principalmente me pergunto se o Oracle não implementa o padrão SQL, ou se o MySQL e o PostgreSQL estendem o padrão).

Tecnicamente, a solução para o meu problema seria uma junção em que ambos, o agregado e o valor adicional são calculados separadamente e, em seguida, combinados na instrução de seleção externa. Meu foco não está em como alguém pode resolver esse problema, mas em estou perdendo alguma coisa no Oracle ou está faltando algo no Oracle? .

Eu tenho um select que procura alguns dados por uma junção. O sistema roda Oracle 10.02g e o select fica assim

SELECT distinct t1.crit
  FROM table_name1 t1
 INNER JOIN table_name2 t2
    ON t1.crit = t2.crit
 WHERE NVL (t2.qty, 0) + NVL (t2.adds, 0) - NVL (t2.remove, 0) > 0
   AND t1.process_id = 'some_process_id'
   AND t1.item_no = 'some item NO'
   AND t1.to_gen = 1

onde t1 possui um índice ativado (item_no, crit) e t2 possui um índice ativado (item_no, crit, X) e X é alguma coluna adicional.

O objetivo desta declaração é encontrar todos os candidatos para os quais preciso gerar dados em tabelas adicionais. Quando os novos dados foram gerados, a coluna t1.to_gen é definida como 0. No momento não está claro se o software que executa esta instrução faz confirmações intermediárias, por exemplo, gera alguns novos dados para um item_no, define t1. to_gen = 0 e confirma, antes de usar o próximo item_no do cursor da instrução em questão.

Esta instrução é executada com sucesso várias vezes ao dia em meu ambiente, mas em casos raros recebo um erro do oracle, cuja fonte não está clara para mim:

ora-01410: invalid RowID

Como a instrução select não usa a pseudocoluna RowID diretamente, o Oracle terá que usá-la internamente.

Qual é a origem deste problema?

Atualização: Por mais triste que pareça, o sistema faz commits intermediários, mas essa não é a fonte do problema.