Problemas com TSQLQuery
08/02/2013
0
for I := 0 to Pred(16) do
begin
datasql := StringReplace(DateToStr(IncDay(now, -I)), '/', '.',
[rfReplaceAll, rfIgnoreCase]);
sql.CommandText := 'SELECT LC.* FROM LOCA_CONTRATO LC WHERE ' +
'EXTRACT (MONTH FROM LC.LOCA_DTINICIO) = EXTRACT (MONTH FROM cast(' +
QuotedStr(datasql) + ' as date)) AND ' +
'EXTRACT (DAY FROM LC.LOCA_DTINICIO) = EXTRACT (DAY FROM cast(' +
QuotedStr(datasql) + ' as date)) AND ' +
'DATEDIFF(YEAR FROM LC.LOCA_DTINICIO TO LC.LOCA_DTTERMINO) > 1 AND ' +
'LC.LOCA_DTINICIO < cast(' + QuotedStr(datasql) + ' as date) AND ' +
'LC.LOCA_DTTERMINO > dateadd(month,11,cast(' + QuotedStr(datasql) +
' as date)) ' +
' AND (SELECT count(*) FROM LOCA_REAJUSTE LR WHERE LR.LOCA_DATA_REAJUSTE = '
+ QuotedStr(datasql) +
' AND LR.LOCA_CONTRATOID = LC.LOCA_CONTRATOID) = 0';
mmo1.Clear;
mmo1.Lines.Add(sql.CommandText);
sql.Open;
if sql.RecordCount > 0 then
showmessage('Retornou algo');
end;
Ao executar o trecho de código acima ele da o erro de "Column Unknow" como se tivesse algo errado no meu SQL, porém peguei o conteúdo gerado no SQL dentro do componente mmo1 (TMemo) e executei no Firebird, e funcionou perfeitamente, abaixo segue o SQL gerado pelo trecho que funcionou no firebird:
SELECT LC.* FROM LOCA_CONTRATO LC WHERE
EXTRACT (MONTH FROM LC.LOCA_DTINICIO) = EXTRACT (MONTH FROM cast('08.02.2013' as date))
AND EXTRACT (DAY FROM LC.LOCA_DTINICIO) = EXTRACT (DAY FROM cast('08.02.2013' as date))
AND DATEDIFF(YEAR FROM LC.LOCA_DTINICIO TO LC.LOCA_DTTERMINO) > 1
AND LC.LOCA_DTINICIO < cast('08.02.2013' as date)
AND LC.LOCA_DTTERMINO > dateadd(month,11,cast('08.02.2013' as date))
AND (SELECT count(*) FROM LOCA_REAJUSTE LR WHERE LR.LOCA_DATA_REAJUSTE = '08.02.2013'
AND LR.LOCA_CONTRATOID = LC.LOCA_CONTRATOID) = 0
Porque o mesmo SQL funciona no Firebird e não funciona no TSQLQUery ?
Ronaldo Lanhellas
Posts
08/02/2013
Ronaldo Lanhellas
sql.CommandText := 'SELECT * FROM LOCA_CONTRATO WHERE ' +
'EXTRACT (MONTH FROM LOCA_DTINICIO) = EXTRACT (MONTH FROM cast(' +
QuotedStr(datasql) + ' as date)) AND ' +
'EXTRACT (DAY FROM LOCA_DTINICIO) = EXTRACT (DAY FROM cast(' +
QuotedStr(datasql) + ' as date)) AND ' +
'DATEDIFF(YEAR FROM LOCA_DTINICIO TO LOCA_DTTERMINO) > 1 AND ' +
'LOCA_DTINICIO < cast(' + QuotedStr(datasql) + ' as date) AND ' +
'LOCA_DTTERMINO > dateadd(month,11,cast(' + QuotedStr(datasql) +
' as date)) ';
08/02/2013
Douglas
vamos fazer um teste
comente esta linha abaixo e tente executar
a Query novamente para averiguarmos.
AND (SELECT count(*) FROM LOCA_REAJUSTE LR WHERE LR.LOCA_DATA_REAJUSTE = + QuotedStr(datasql) + AND LR.LOCA_CONTRATOID = LC.LOCA_CONTRATOID) = 0;
Eu acho que o erro esta no seu subselect, mas vamos testar antes.
08/02/2013
Ronaldo Lanhellas
sql.CommandText := 'SELECT LC.* FROM LOCA_CONTRATO LC WHERE ' +
'EXTRACT (MONTH FROM LC.LOCA_DTINICIO) = EXTRACT (MONTH FROM cast(' +
QuotedStr(datasql) + ' as date)) AND ' +
'EXTRACT (DAY FROM LC.LOCA_DTINICIO) = EXTRACT (DAY FROM cast(' +
QuotedStr(datasql) + ' as date)) AND ' +
'DATEDIFF(YEAR FROM LC.LOCA_DTINICIO TO LC.LOCA_DTTERMINO) > 1 AND ' +
'LC.LOCA_DTINICIO < cast(' + QuotedStr(datasql) + ' as date) AND ' +
'LC.LOCA_DTTERMINO > dateadd(month,11,cast(' + QuotedStr(datasql) +
' as date)) ';
Porém o erro continua.
08/02/2013
Ronaldo Lanhellas
10/02/2013
Marco Salles
for I := 0 to Pred(16) do
vc esta dando sql.Open quinze vezes seguidamente ????
18/02/2013
Ronaldo Lanhellas
Obrigado pela ajuda.
Clique aqui para fazer login e interagir na Comunidade :)
