Firebird, Joins, Nulls and Order By.

rbuser

This is a bit of info for anyone using Firebird 1.5 with Report
builder. I have spent several days trying to work out why master detail
reports were giving totally wrong results.
The problem is that the master dataset has to be sorted by various
fields, all of which can contain nulls. The basic problem lies in the
fact that Report builder assumes that all nulls appear FIRST in the
sorted result set. This is a bad assumption for firebird which, by
default places nulls LAST.
I have managed to cure the problem by adding the following line to
BuildOrderByClause in daSQL.pas
I would like to know if there are any more robust solutions to this
which would allow someone to use both interbase and firebird because I
don't think interbase supports the 'nulls first' syntax.

Regards.
Will.

procedure TdaSQL.BuildOrderByClause;
var
liIndex: Integer;
lField: TdaField;
lsText: String;
begin

if (OrderByFieldCount <> 0) and not(FStealthMode) then
begin
FSQLText.Add('ORDER BY ');
FIndent := 9;

for liIndex := 0 to OrderByFieldCount - 1 do
begin
lsText := '';

lField := OrderByFields[liIndex];

if lField is TdaCalculation then
{note: do not use lField.Index, because it will return the
OrderByFields.IndexOf value}
lsText := IntToStr(SelectFieldCount + daFindField(lField,
FCalcFields) + 1)
else
lsText := lField.SQLString;

if not(lField.Ascending) then
lsText := lsText + ' DESC';

{**********ADD THIS}
if IsInterBase then
lsText := lsText + ' Nulls First' ;
{******************}

DoAddOrderByClauseText(Self, lField, lsText);

if (liIndex < OrderByFieldCount - 1) then
lsText := lsText + ', ';

AddToSQL(lsText);
end;
end;

end; {procedure, BuildOrderByClause}

nicocizik

Hi Will,

Thank you for the information. We will research this and look into fixing
it for the next release.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com