--- In IBObjects@yahoogroups.com, "Paul Hope" <paulhope@...> wrote:

>
> Pretty sure SQLOrder should include 'Order By'
>
> Paul
>
> > -----Original Message-----
> > From: IBObjects@yahoogroups.com
> > [mailto:IBObjects@yahoogroups.com] On Behalf Of mitch_landor
> > Sent: 14 December 2007 12:57
> > To: IBObjects@yahoogroups.com
> > Subject: [IBO] TIBOQuery: Can't get SqlOrder.add to work
> >
> > 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?
> > Thanks
> > Mitch
> >

>

Thanks Paul, this seems to work (see code below). I have also added
spaces and commas as necessary, which is not mentioned in the Getting
Started guide.

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.