Hi Andrus,
Which database is the exception? How do the other databases sort NULL
columns?
Anyway, you can simply override the method name GetOrderByClause() in the
PostgreSQL vendor, but I'm not sure it's a good idea to do this by default,
since some users may want to use the defautl feature.
Ideally, we would need an option (in nonstrict mode) to control this
behavior. Or maybe a dummy method that could be applied to sort expressions,
and we would get expressions such as "orderby NullsFirst(table.column)" (our
special method is NullsFirst here). All of this available only in non-strict
mode.
What do you think?
Pascal.
On Tue, Sep 16, 2008 at 10:29, Andrus <[EMAIL PROTECTED]> wrote:
>
> 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();
> }
>
>
> >
>
--
Pascal.
jabber/gtalk: [EMAIL PROTECTED]
msn: [EMAIL PROTECTED]
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
