SQL don't return rows

[mufalo]

Hi !
if execute this code, don't return any row..
qryLivro.SQL.Clear;
qryLivro.SQL.Add('SELECT ');
qryLivro.SQL.Add('NF.NF_CODIGO, NF.NF_DT_ENTRADA, NF.NF_SERIE, Nf.NF_NUM, NF.NF_DT_EMISSAO,');
qryLivro.SQL.Add('FORN.FOR_CODIGO, MUN.MUN_UF, PLA.PLA_NUM,NFP.NFPRO_CLC, TPD.TPD_SIGLA, NF_NRCONHEC, ');
qryLivro.SQL.Add('NF.PLA_CODIGO_FRETE, NF.NF_VLFRETE, NF.NF_FRETE_CLASSCONT, PLAFRETE.PLA_NUM as PLA_NUMFRETE, ');
qryLivro.SQL.Add('SUM(NFP.NFPRO_QTDE*NFP.NFPRO_VLR) as NF_VALCONT');
qryLivro.SQL.Add('FROM NOTAFISCAL NF');
qryLivro.SQL.Add('JOIN FORNECEDOR FORN ON (NF.FOR_CODIGO = FORN.for_codigo)');
qryLivro.SQL.Add('LEFT OUTER JOIN MUNICIPIO MUN ON (FORN.mun_codigo = MUN.MUN_CODIGO)');
qryLivro.SQL.Add('JOIN NF_PRO NFP ON (NFP.NF_CODIGO = NF.NF_CODIGO)');
qryLivro.SQL.Add('LEFT OUTER JOIN PLACON PLA ON (NFP.PLA_CODIGO = PLA.PLA_CODIGO)');
qryLivro.SQL.Add('LEFT OUTER JOIN PLACON PLAFRETE ON (NF.PLA_CODIGO_FRETE = PLAFRETE.PLA_CODIGO)');
qryLivro.SQL.Add('LEFT OUTER JOIN TPDOC TPD ON (TPD.TPD_CODIGO = NF.TPD_CODIGO)');
qryLivro.SQL.Add('JOIN CFOP ON (CFOP.CFOP_CODIGO = NF.CFOP_codigo)');
qryLivro.SQL.Add('WHERE NF.NF_LIVROFISCAL = ''S''');
qryLivro.SQL.Add(' AND NF_DT_ENTRADA BETWEEN :NF_DT_ENTRADAINI AND :NF_DT_ENTRADAFIM');
qryLivro.SQL.Add(' AND NF.EMP_CODIGO = :EMP_CODIGO');
qryLivro.SQL.Add('GROUP BY ');
qryLivro.SQL.Add('NF.NF_CODIGO, NF.NF_DT_ENTRADA, NF.NF_SERIE, Nf.NF_NUM, NF.NF_DT_EMISSAO,');
qryLivro.SQL.Add('FORN.FOR_CODIGO, MUN.MUN_UF, PLA.PLA_NUM,NFP.NFPRO_CLC, TPD.TPD_SIGLA, NF_NRCONHEC,');
qryLivro.SQL.Add('NF.PLA_CODIGO_FRETE, NF.NF_VLFRETE, NF.NF_FRETE_CLASSCONT, PLAFRETE.PLA_NUM');
qryLivro.SQL.Add('order by nf.nf_dt_entrada, NF.nf_num');
qryLivro.ParamByName('NF_DT_ENTRADAINI').AsDateTime:=EncodeDate(2005,01,01);
qryLivro.ParamByName('NF_DT_ENTRADAFIM').AsDateTime:=EncodeDate(2005,01,30);
qryLivro.ParamByName('EMP_CODIGO').AsInteger:=1;
qryLivro.Open;


but, after any change on the instruction, like a space in some part, return rows.
Example:
Change:
qryLivro.SQL.Add('FROM NOTAFISCAL NF');
to
qryLivro.SQL.Add(' FROM NOTAFISCAL NF');

will return rows.

[Alex]

Please reduce your sample size and try to use default tables (i.e. Scott.Dept) in it.

[mufalo]

Please reduce your sample size and try to use default tables (i.e. Scott.Dept) in it.

I can't reproduce the error in other cases.
I can generate a sample application to show this error.
How I send this sample application to you ?


Sorry by my poor english.

[mufalo]

I found this Bug(?) in this case too:

SELECT T.TIT_NUM, T.TIT_DTEMISSAO, T.TIT_DTVENC, T.TIT_VALOR,
CRED.PLA_NOME AS CREDITO, PAG.PLA_NOME AS PAGADOR, EMP.EMP_NOME,
T.TIT_DTAUTORIZA, T.TIT_USUAUTORIZA, T.TIT_MOTIVOAUTORIZA,
T.TIT_CODIGO, T.TIT_AUTORIZADO, T.TIT_DTSOLICITA, T.TIT_USUSOLICITA,
USU.USU_NOME, SOL.USU_NOME AS SOL_NOME,
TPP.TPP_NOME
FROM TITULO T
JOIN TIPOPAGTO TPP ON (T.TPP_CODIGO = TPP.TPP_CODIGO)
JOIN PLACON CRED ON (T.TIT_CONCRED = CRED.PLA_CODIGO)
JOIN EMPRESA EMP ON (T.EMP_CODIGO = EMP.EMP_CODIGO)
JOIN PLACON PAG ON (T.TIT_BANPAG = PAG.PLA_CODIGO)
JOIN USUARIO USU ON (T.TIT_USUAUTORIZA = USU.USU_CODIGO)
JOIN USUARIO SOL ON (T.TIT_USUSOLICITA = SOL.USU_CODIGO)
WHERE (T.TIT_DTPAGTO IS NULL)
AND (T.TIT_AUTORIZADO = 'S')
AND (T.TPP_CODIGO = :TPP_CODIGO)
AND T.TIT_DTEMISSAO BETWEEN :TIT_DTEMISSAOINI AND :TIT_DTEMISSAOFIM


but, with this, returns 33 rows
:TPP_CODIGO(INTEGER)=1
:TIT_DTEMISSAOINI()='01/11/2005'
:TIT_DTEMISSAOFIM()='30/11/2005'

with this parameters, doesn't return rows.
:TPP_CODIGO(INTEGER)=1
:TIT_DTEMISSAOINI()='01/01/2005'
:TIT_DTEMISSAOFIM()='31/12/2005'

this date are in format dd/mm/yyyy

the first period is more restricted than second, but none row is returned.

As I already said, I did not obtain to simplify the instruction, but if I to place plus a filter, the data will be returned correctly.

[Alexandre]

Dear mufalo,

If you analyze your query, you'll see this issue:

This code

qryLivro.SQL.Add('SUM(NFP.NFPRO_QTDE*NFP.NFPRO_VLR) as NF_VALCONT');
qryLivro.SQL.Add('FROM NOTAFISCAL NF');

Will be

SUM(NFP.NFPRO_QTDE*NFP.NFPRO_VLR) as NF_VALCONTFROM NOTAFISCAL NF

Not a valid sql sentence.

when you put the space become this

SUM(NFP.NFPRO_QTDE*NFP.NFPRO_VLR) as NF_VALCONT FROM NOTAFISCAL NF

A valid sql sentence.

Hope helped you.

Alexandre[/code]