At 11:24 AM 10/07/2003 -0300, you wrote:

>Delphi 6 SP 2, IBO 4.2Ib, Firebird 1.03.972 and Firebird 1.5 RC 4, Win 98
>
>Hello to All. Greetings.
>
>We have this "very simple" select with one subselect.
>
>Select Count(Distinct NumLote) as Total,
>(Select Count(distinct NumLote) from Co_Lct Where ((CodEmpresa=:CodEmpresa)
>and (Identificacao starting with 'LA'))) as TotLote
>from Co_Lct where ((CodEmpresa=:CodEmpresa) and (Data between :DataInicial
>and :DataFinal))
>
>Have only one table used: Co_Lct with this structure:
>CodEmpresa SmallInt,
>NumLote Integer,
>Data Date,
>Identificacao Varchar(18)
>
>With no Primary Key, and various (non-unique) Index or Foreign Keys.
>
>Some times, this select not work in IBO (checking this query in the
>queryeditor - Params) it shows parameters as:
>CodEmpresa Date,
>DataInicial smallint,
>DataFinal Date,
>CodEmpresa smallint.
>
>And the most strange is: when we do a database.disconnect, followed by
>database.connect, it works fine!
>
>Fragment of Delphi Code:
>
>With DM.QyAux do
> begin
> If Active then Close;
> UnPrepare;
> OldParamValueLinks.Clear;
> KeyLinksAutoDefine := True;
> FetchWholeRows := True;
> OrderingItems.Clear;
> OrderingLinks.Clear;
> KeyLinks.Clear;
> SQL.Clear;
> Sql.Add( 'Select Count(Distinct NumLote) as Total,');
> Sql.Add( '(Select Count(distinct NumLote) from Co_Lct Where
>((CodEmpresa=:CodEmpresa) and (Identificacao starting with ''LA''))) as
>TotLote');
> Sql.Add( 'from Co_Lct where ((CodEmpresa=:CodEmpresa) and (Data between
>:DataInicial and :DataFinal))');
> Prepare;
> ParamByName('CodEmpresa').AsInteger := VarPub.CodEmpresa; // <---
>Error message ('14' is not a valid date ...) at this point.
> ParamByName('DataInicial').AsDate := StrToDate(EdDataInicial.Text);
> ParamByName('DataFinal').AsDate := StrToDate(EdDataFinal.Text);
> Open;
> First;
> end;
>
>How resolve it ?

I think you will find that your problems will go away when you correct your
SQL. It is ambiguous. Later builds of Firebird will choke on that statement.

Here's what you need for your "NOT very simple" select with one subselect:

Select Count(Distinct cl1.NumLote) as Total,
(Select Count(distinct cl2.NumLote) from Co_Lct cl2
Where ((cl2.CodEmpresa=:CodEmpresa)
and (cl2.Identificacao starting with 'LA'))) as TotLote
from Co_Lct cl1
where ((cl1.CodEmpresa=:CodEmpresa)
and (cl1.Data between :DataInicial and :DataFinal)) /* note removed brackets */

Have only one table used: Co_Lct with this structure:
CodEmpresa SmallInt,
NumLote Integer,
Data Date,
Identificacao Varchar(18)

Because of the correlated subquery, you need to have two cursors on the
table - hence the need to use column-identifiers.

Helen