Since the MSSQL's ISNULL function isn't supported by LLBLGen Pro (1.2005.1) I'm stuck. Can anyone tell me is it possible to write SortExpression to sort this table?

TABLE1:

A B(PK)

NULL 1 NULL 2 NULL 3 1 4

SELECT  A, B
FROM    TABLE1
ORDER BY ISNULL (A, B)

SORTED TABLE1:

A B

NULL 1 1 4 NULL 2 NULL 3

Thanks, Vlada

Perhaps I'm not getting your table, but why is: SORTED TABLE1:

A B

NULL 1 1 4 NULL 2 NULL 3

called 'sorted', as to me it looks like it's in random order? Could you elaborate a bit more on this?

Otis wrote:

Perhaps I'm not getting your table, but why is: SORTED TABLE1:

A B

NULL 1 1 4 NULL 2 NULL 3

called 'sorted', as to me it looks like it's in random order? Could you elaborate a bit more on this?

If you look at his SELECT, he sorts by A, unless it is null, then it sorts by B.

BOb

pilotboba wrote:

Otis wrote:

Perhaps I'm not getting your table, but why is: SORTED TABLE1:

A B

NULL 1 1 4 NULL 2 NULL 3

called 'sorted', as to me it looks like it's in random order? Could you elaborate a bit more on this?

If you look at his SELECT, he sorts by A, unless it is null, then it sorts by B.

Yes I know, but I wanted to understand the semantic value of that, as I don't see it yet (understand it yet) (and by understanding the semantic value I mean: so I can give an alternative perhaps)

Otis wrote:

Yes I know, but I wanted to understand the semantic value of that, as I don't see it yet (understand it yet) (and by understanding the semantic value I mean: so I can give an alternative perhaps)

I see. He could probably just add a calculated field to his table, or create a view to solve this problem though.

BOb

pilotboba wrote:

Otis wrote:

Yes I know, but I wanted to understand the semantic value of that, as I don't see it yet (understand it yet) (and by understanding the semantic value I mean: so I can give an alternative perhaps)

I see. He could probably just add a calculated field to his table, or create a view to solve this problem though.

I’ll now try to be more explicit,

Definition of Table1:

CREATE TABLE [dbo].[Table1] (
    [A] [int] NULL ,
    [B] [int] NOT NULL 
) ON [PRIMARY]

ALTER TABLE [dbo].[Table1] WITH NOCHECK ADD 
    CONSTRAINT [PK_Table1] PRIMARY KEY  CLUSTERED 
    (
    [B]
    )  ON [PRIMARY] 

Column B is primary key, Column A value is NULL in case of root entry (root entry is row without superior row) or it holds superiors row primary key value. Superior row is always root entry. In the given example rows with PK values 1, 2, 3 are root entries and row with PK value 4 has superior entry with PK value 1. Entries with PK 1 and 4 are linked in the sense that row 1 is root entry and row 4 has 1 as its superior.

In the following code block is query without order clause and query result:

SELECT A, B
FROM   TABLE1

A          B        
----------- ----------- 
NULL        1
NULL        2
NULL        3
1             4

(4 row(s) affected)

In the following code block is query with order clause and query result:

SELECT A, B
FROM   TABLE1
ORDER BY ISNULL (A, B)

A          B        
----------- ----------- 
NULL        1
1             4
NULL        2
NULL        3

(4 row(s) affected)

Purpose of order clause in the given query is to sort rows by root entry row followed by rows which have that root row as superior.

Any suggestions how to write SortExpression?

Thanks, Vlada

May I ask what will you gain from sorting the resultset that way?

If field A is a FK on the same table, you can return results of Root Rows only with their child rows enclosed in a collection property (using prefechpaths).

Walaa wrote:

May I ask what will you gain from sorting the resultset that way?

If field A is a FK on the same table, you can return results of Root Rows only with their child rows enclosed in a collection property (using prefechpaths).

I need to preview entire table sorted in the manner I explained. I already managed that by fetching data into DataTable and sorting DataTable. Now I'm looking for solution to sort data in fetch. I'm wondering is it possible or not?

Thanks, Vlada

Not with ISNULL(A, B) at the moment and llblgen pro, which was the reason I tried to understand why the data is sorted the way you explained so there might be a different way to get the data out the db in such a way that it is workable for you. Unless you need hte data exactly in this format, I don't see a solution for this.

My question is similar but I can't find an answer in this thread: Is it (at all) possible to fetch an EntityCollection and sort it using an (no matter what) (I)Expression? I tried to use ExpressionToApply in the SortClause, but the generated SQL contained the alias of the field in stead of my expression.

Peter wrote:

My question is similar but I can't find an answer in this thread: Is it (at all) possible to fetch an EntityCollection and sort it using an (no matter what) (I)Expression? I tried to use ExpressionToApply in the SortClause, but the generated SQL contained the alias of the field in stead of my expression.

No that's currently not supported.