PostgreSQL ORDER BY treats NULL columns as greater of all other column by
default.
MS SQL treats NULL column as less than all other columns.
So order by causes different results.
PostgreSQL 8.1 adds NULLS FIRST / NULLS LAST clauses which can be used to
change this.
For asc order
ORDER BY c1 NULLS FIRST, c2 NULLS FIRST
and for descending order
ORDER BY c1 DESC NULLS LAST, c2 DESC NULLS LAST
I changed old engine BuildSqlString in Vendor.cs to support this as code
below.
However this works only in PostgreSQL .
How to change Vendor base class so that it allows to generate such order by
clause by vendor ?
Andrus.
public string BuildSqlString(SqlExpressionParts parts)
...
if (parts.CountClause != "COUNT")
{
// am. added.
bool orderByPresent = parts.OrderByList != null &&
parts.OrderByList.Count != 0;
bool desc = parts.OrderDirection != null &&
parts.OrderDirection.Trim().ToUpperInvariant() == "DESC";
string orderByAdd = "";
if (true) // AM todo: add check for serverversion>8.0 here
if (desc)
orderByAdd = " NULLS LAST ";
else
orderByAdd = " NULLS FIRST ";
if (orderByPresent)
AppendList(sql, " ORDER BY ", parts.OrderByList,
" " + (parts.OrderDirection ?? "") + orderByAdd + ",
");
// original code:
//AppendList(sql, " ORDER BY ", parts.OrderByList, ", ");
//if (parts.OrderDirection != null)
// sql.Append(' ').Append(parts.OrderDirection).Append('
'); //' DESC '
// am. added
if (orderByPresent)
sql.Append(" " + (parts.OrderDirection ?? "") +
orderByAdd);
else
sql.Append(' ');
}
AddLateLimits(sql, parts);
return sql.ToString();
}
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"DbLinq" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/dblinq?hl=en
-~----------~----~----~----~------~----~------~--~---
