Ora-00979

[adrianopad 0]

Senhores(a)

Ao Executar o Script na base de dados usando PLSQL, é retornado os resultados, quando colo no delphi 7 da erro de ORA-00979. Tem ideia do que pode ser:

 

Segue Script PLSQL:

**********************************************

select crd_snr,

count(crd_snr) as QTD

 

from CardUsageValidPassengers

 

where

cd_id = 4

and app_id in (400,500)

and cu_datetime > = to_date('13/02/2011 04:00:00','dd/mm/yyyy hh24:mi:ss')-- Data inicial

and cu_datetime < to_date('14/02/2011 03:59:59','dd/mm/yyyy hh24:mi:ss')-- Data Final

and cu_tpid in (1,2,3) --

and cu_tsn <> 0

group by crd_snr

HAVING COUNT(crd_snr) > 8

order by 2 desc

 

**********************************************

DELPHI 7

 

with qryUsoOk do begin

Close;

SQL.Clear;

SQL.Add('SELECT CRD_SNR, COUNT (*) AS QTD ');

SQL.Add('FROM CARDUSAGEVALIDPASSENGERS ');

SQL.Add('WHERE CD_ID = :CD_ID AND ');

SQL.Add(' APP_ID = :APP_ID AND ');

SQL.Add(' CU_DATETIME BETWEEN :DATAMINICIO AND :DATAMFIM AND ');

SQL.Add(' CU_TPID IN (1,2,3) AND ');

SQL.Add(' CU_TSN <> 0 ');

SQL.Add('GROUP BY CRD_SNR ');

SQL.Add('HAVING COUNT (*) > :QTD ');

SQL.Add(' ORDER BY CRD_SNR DESC ');

Parameters.ParamByName('CD_ID').Value := txtTipoCartao_CD_ID.Text;

Parameters.ParamByName('APP_ID').Value := txtAolicacaoUsoOk_APP_ID.Text;

Parameters.ParamByName('DATAMINICIO').Value := dtpDataInicial_CU_DATETIME.DateTime;

Parameters.ParamByName('DATAMFIM').Value := dtpDataInicial_CU_DATETIME.DateTime;

Parameters.ParamByName('QTD').Value := txtQTDUsoOK.Text;

Open;

end;

end;

[supernoi 0]

ORA-00979:not a GROUP BY expression

 

Cause: The GROUP BY clause does not contain all the expressions in the SELECT clause. SELECT expressions that are not included in a group function, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, must be listed in the GROUP BY clause.

 

Action: Include in the GROUP BY clause all SELECT expressions that are not group function arguments.

[Motta 645]

Parece certo , salve o Sql para texto e rode o sql salvo.

 

SQL.SaveToFile('...');