Devart Forums

Hello,
I have posted with a error i´m getting, but now the error is a lot diferent.

if execute this SQL:
SELECT
NF.NF_CODIGO, NF.NF_DT_ENTRADA, NF.NF_SERIE, Nf.NF_NUM, NF.NF_DT_EMISSAO,
FORN.FOR_CODIGO, MUN.MUN_UF, NFP.NFPRO_CLC, TPD.TPD_SIGLA, NF_NRCONHEC,
NF.NF_VLFRETE, NF.NF_FRETE_CLASSCONT, NF.NF_OBSLIVRO,
SUM(NFP.NFPRO_QTDE*NFP.NFPRO_VLR) as NF_VALCONT
FROM NOTAFISCAL NF
JOIN FORNECEDOR FORN ON (NF.FOR_CODIGO = FORN.for_codigo)
LEFT OUTER JOIN MUNICIPIO MUN ON (FORN.mun_codigo = MUN.MUN_CODIGO)
JOIN NF_PRO NFP ON (NFP.NF_CODIGO = NF.NF_CODIGO)
LEFT OUTER JOIN TPDOC TPD ON (TPD.TPD_CODIGO = NF.TPD_CODIGO)
JOIN CFOP ON (CFOP.CFOP_CODIGO = NF.CFOP_codigo)
WHERE NF.NF_LIVROFISCAL = 'S'
AND NF_DT_ENTRADA BETWEEN :NF_DT_ENTRADAINI AND :NF_DT_ENTRADAFIM
AND NF.EMP_CODIGO = :EMP_CODIGO
GROUP BY
NF.NF_CODIGO, NF.NF_DT_ENTRADA, NF.NF_SERIE, Nf.NF_NUM, NF.NF_DT_EMISSAO,
FORN.FOR_CODIGO, MUN.MUN_UF, NFP.NFPRO_CLC, TPD.TPD_SIGLA, NF_NRCONHEC,
NF.NF_VLFRETE, NF.NF_FRETE_CLASSCONT, NF.NF_OBSLIVRO
order by nf.nf_dt_entrada, NF.nf_num desc

With this CODE:

Query.SQL.Text := Memo1.Lines.Text;
Query.FetchAll := True;
Query.ParamByName('NF_DT_ENTRADAINI').AsDateTime := EncodeDate(2004, 12, 1);
Query.ParamByName('NF_DT_ENTRADAFIM').AsDateTime := EncodeDate(2005, 12, 31);
Query.ParamByName('EMP_CODIGO').AsInteger := 1;
Query.Open;
DataSource1.DataSet:=Query;
Edit1.Text := IntToStr(Query.RecordCount);

The Return of Edit1.Text is 37.

if I execute this SQL, with DB Express using crlab driver,
the return is 5831.

I think this is a bug with ODAC, because the result of SQL in DB Express is the correct, and if I make a mininal change in the SQL instruction, like change "order by nf.nf_dt_entrada, NF.nf_num desc" by
"order by nf.nf_dt_entrada, NF.nf_num desc"
the result of SQL execute in ODAC, is correct.

I will be create a sample project, with script to create tables and datas to show this bug.


Thank you in advance.
Adriano

Please reduce your sample to acceptable size, and, if it possible, send us small project to demonstrate your problem by email to ODAC support address.

I guess this can be bug in Oracle (9.0.1.0.0),
because before I execute:
analyze table NOTAFISCAL compute statistics;

the result of SQL is correct.