--- In IBObjects@yahoogroups.com, Helen Borrie <helebor@...> wrote:

>
> At 11:56 PM 14/12/2007, you wrote:
> >I've never used SQLOrder.Add before and I can't understand why this
> >SQL will work in my TIBOQuery:
> >'ORDER BY docline.ITEM_NO_CHARGE_TO_CLIENT,
> >costtype.costtype_sortorder, loc.loc_sortorder, loc.loc_name,
> >docline.item_order'
> >
> >but when I try to use SQLOrder.Add I get the error message shown below
> >
> >procedure TfrmDocument.qryEstimateDataPrepareSQL(Sender: TObject);
> >begin
> > with qryEstimateData do
> > begin
> > //Set the sort order
> > SQLOrder.Add('docline.ITEM_NO_CHARGE_TO_CLIENT');
> > SQLOrder.Add('costtype.costtype_sortorder');
> > SQLOrder.Add('loc.loc_sortorder');
> > SQLOrder.Add('loc.loc_name');
> > SQLOrder.Add('docline.item_order');
> > end;
> >end;
> >
> >ISC ERROR MESSAGE:
> >Dynamic SQL Error
> >SQL error code = -104
> >Token unknown - line 29, column 1
> >docline
> >
> >STATEMENT:
> >TIBOInternalDataset:
> >"<TApplication>.frmDocument.qryEstimateData.IBOqrqryEstimateData."
> >'.
> >
> >Can anyone suggest why this is not working please?
>
> I notice you don't clear the SQLOrder array before you apply the new

ordering criteria. Possibly docline.item_order is already in the
array from the previous prepare?

>
> Helen
>

Helen,

I am closing the dataset elsewhere beforehand and the Getting Started
Guide says
"SQLOrder.Clear is unnecessary because Prepare will do it
automatically".

The code below seems to work. I have also added
spaces and commas as necessary, which is not mentioned as necessary in
the Getting Started Guide. Can you confirm that "Order By" must always
be explicitly added (as in SQLOrder.Add('order by ')), or can this be
automatically added by the prepare process and am I missing something?

procedure TfrmDocument.qryEstimateDataPrepareSQL(Sender: TObject);
begin
with qryEstimateData do
begin
//Set the sort order
SQLOrder.Add('order by ');
SQLOrder.Add('docline.ITEM_NO_CHARGE_TO_CLIENT,');
SQLOrder.Add('costtype.costtype_sortorder,');
SQLOrder.Add('loc.loc_sortorder,');
SQLOrder.Add('loc.loc_name,');
SQLOrder.Add('docline.item_order');
end;
end;

The Getting Started Guide seems incorrect and suggests that no "Order
By" or commas are needed and that the prepare puts these in
automatically. It says:
Quote:
For example, you might prefer to leave the SQL statement without any
clauses to qualify criteria. At run-time, in the OnPrepareSQL event
handler, you can set the properties SQLWhereItems and SQLOrder to
"fill in" WHERE and ORDER BY clauses:
e.g.
procedure TDataModule1.qryComponentDataPrepareSQL(Sender: TIB_Statement);
begin
...
with qryComponentData do

begin
// SQLWhereItems.Clear is unnecessary because Prepare will do it
automatically
SQLWhereItems.Add('ComponentID = :ComponentID');
SQLWhereItems.Add('(CheckoutDate >=(CAST('TODAY' AS DATE)) - 7))' );
// SQLOrder.Clear is unnecessary because Prepare will do it
automatically

SQLOrder.Add('ComponentID');

SQLOrder.Add('CheckedOutNumber DESC');
end;

...
end;

This piece of code in immediately sets the SQL in the example above to

SELECT ComponentID, Designator2, Title, ShortText, CheckedOutNumber
FROM ComponentData
WHERE ComponentID = :ComponentID
AND (CheckoutDate >=(CAST('TODAY' AS DATE)) - 7))
ORDER BY ComponentID, CheckedOutNumber DESC

Endquote.