Hello Helen!

It's always a priviledge to talk to people who knows what they're
doing. ;-)
All the tips were great. But, I'm afraid I've left out some important
information in order to simplify things.

Here goes the whole story:

The "main" table here is indeed a detail from a master-detail,
connected via MasterLinks (although this may not do any difference).

The real table has 3 date fields and 2 foreign keys, any of these
folks may be "parameterized" and that's the origin of my use of
SQLWhereItems/InvalidateSQL.

The complete OnPrepareSQL follows:
//---------------------------------------------------------------------------
void __fastcall TFormCadInstalacoes::IB_QueryOcorrenciasPrepareSQL(
TIB_Statement *Sender)
{
AnsiString fieldName;

switch (ListBoxDateFields->ItemIndex) {

case 0: fieldName = "DATA_ENTRADA";
break;
case 1: fieldName = "DATA_RECONHECIMENTO";
break;
case 2: fieldName = "DATA_ATENDIMENTO";
break;
}

IB_QueryOcorrencias->SQLWhereItems->Add(fieldName + " >=
:data_inicial");
IB_QueryOcorrencias->SQLWhereItems->Add(fieldName + " <=
:data_final");

if (IB_CBClasseOcorrencia->ItemIndex != -1)
IB_QueryOcorrencias->SQLWhereItems->Add("id_classe_ocorrencia
= :id_classe_ocorrencia");

if (IB_CBCodigoOcorrencia->ItemIndex != -1)
IB_QueryOcorrencias->SQLWhereItems->Add("id_ocorrencia =
:id_ocorrencia");

}
//---------------------------------------------------------------------------

The tricky part is that the date fields are working but the Combos
don't. I didn't found many docs about the use of these features but
may be I'm missing the right ordering of events (or perhaps just
completely lost and confused %-).

By the way, IBO is 4.3.Aa.

Tnx
Alexandre

--- In IBObjects@yahoogroups.com, Helen Borrie <helebor@...> wrote:
>
> At 11:10 AM 11/05/2006, you wrote:
>
> >Ok, sorry, only now I've realized it could be more verbose ;-)
>
> Yes. The problems become immediately evident.
>
>
> >The main query - IB_QueryOcorrencias:
> >-----------------------------------
> >select *
> >from LOG_OCORRENCIAS lo
> > join CAD_OCORRENCIAS co on lo.id_ocorrencia = co.id_ocorrencia
> > join cad_classe_ocorrencias cco on co.id_classe_ocorrencia =
> >cco.id_classe
>
> First problems occur with the main query: the syntax is bad. Rewrite
> it so that
>
> (a) it has a column list; and
> (b) each output column is properly identified.
>
> For example,
>
> select
> lo.id_ocorrencia,
> lo.field1,
> co.fieldx,
> cco.field_blah
> from LOG_OCORRENCIAS lo
> join CAD_OCORRENCIAS co on lo.id_ocorrencia = co.id_ocorrencia
> join cad_classe_ocorrencias cco on co.id_classe_ocorrencia =
> cco.id_classe
>
>
> >The ComboBox query - IB_QueryClasseOcorrencia:
> >-----------------------------------
> >SELECT ID_CLASSE, DESCRICAO
> >FROM CAD_CLASSE_OCORRENCIAS
> >
> >The code that loads the values and item values:
> >-----------------------------------------------
> >void __fastcall TFormCadInstalacoes::LoadCBoxClasseOcorrencia(void)
> >{
> > IB_CBClasseOcorrencia->Items->Clear();
> > IB_CBClasseOcorrencia->ItemValues->Clear();
> > IB_QueryClasseOcorrencia->Open();
> > while (!IB_QueryClasseOcorrencia->Eof) {
>
>IB_CBClasseOcorrencia->Items->Add(IB_QueryClasseOcorrencia->FieldByName("DESCRICAO")->AsString);
> >
>
>IB_CBClasseOcorrencia->ItemValues->Add(IB_QueryClasseOcorrencia->FieldByName("ID_CLASSE")->AsString);
> > IB_QueryClasseOcorrencia->Next();
> > }
> > IB_QueryClasseOcorrencia->Close();
> >}
>
> This approach is inefficient. Use a TIB_Cursor to load the Items.
> Let's suppose you make ib_CBClasseOcorrencia an IB_Cursor object.
>
> IB_CBClasseOcorrencia->Items->Clear();
> IB_CBClasseOcorrencia->ItemValues->Clear();
> IB_QueryClasseOcorrencia->First();
<-------------------------------------
> while (!IB_QueryClasseOcorrencia->Eof) {
>
IB_CBClasseOcorrencia->Items->Add(IB_QueryClasseOcorrencia->FieldByName("DESCRICAO")->AsString);
>
>
IB_CBClasseOcorrencia->ItemValues->Add(IB_QueryClasseOcorrencia->FieldByName("ID_CLASSE")->AsString);
> IB_QueryClasseOcorrencia->Next();
> }
> IB_QueryClasseOcorrencia->Close();
> }
> The event that adds the parameter:
> >-----------------------------------
> >void __fastcall
> >TFormCadInstalacoes::IB_QueryOcorrenciasPrepareSQL(TIB_Statement
*Sender)
> >{
> > if (IB_CBClasseOcorrencia->ItemIndex != -1)
> > IB_QueryOcorrencias->SQLWhereItems->Add("id_classe_ocorrencia
> >= :id_classe_ocorrencia");
> >}
>
> No.
> (1) This is not the right usage of SQLWhereItems. You use that when
> you actually want to change the WHERE criteria (which you don't,
> here) AND you have do this in the OnPrepareSQL event.
>
> (2) What you REALLY want here is to place a parameterised WHERE
> clause in the main query - since you are "setting it in concrete"
> anyway, by binding it to your combobox.
>
> So, here is how your your main query needs to be:
>
> select
> lo.id_ocorrencia,
> lo.field1,
> co.fieldx,
> cco.field_blah
> from LOG_OCORRENCIAS lo
> join CAD_OCORRENCIAS co on lo.id_ocorrencia = co.id_ocorrencia
> join cad_classe_ocorrencias cco on co.id_classe_ocorrencia =
> cco.id_classe
> where cco.id_classe_ocorrencia = :id_classe_ocorrencia
>
> (Sorry if I have guessed wrong about which table has
> id_classe_ocorrencia, but you get the picture.)
>
>
>
> >Propesties of the IB_ComboBox:
> >------------------------------
> >DataField = blank
> >DataSource= IB_DataSourceOcorrencias (which is connected to
> >IB_QueryOcorrencias)
> >ParamName = id_classe_ocorrencia
> >
> >When the user select the tab where are the controls, the program runs
> >like this:
> >
> >LoadCBoxClasseOcorrencia();
> >IB_QueryOcorrencias->InvalidateSQL();
>
> No, don't call InvalidateSQL. **You are NOT changing the parameter,
> only its value.**
>
> What you actually want is to start with the entire main query
> (INCLUDING the where clause), prepare it, and thereafter to apply a
> new *value* to the parameter by way of the combobox selection.
>
> >IB_QueryOcorrencias->Active = true;
>
> Don't open queries by setting Active true. Use Active when you want
> to to test whether an IB_Query is active; use Open on an IB_Query
> the *first time* and Refresh all other times; use First on an
IB_Cursor.
>
>
> >And nothing is showed in the Combo.
> >I hope not throwed out too much information this time. ;-)
>
> This time it was adequate. :-)
>
> Helen
>