Hi

I have a problem with sorting I have the following code

The database tabels relates: Clinc -> Address -> Country

Both Country and Clinic has a field "Name". The problem is that LLBL always sort on the Clinc.Name and not Country.Name. The only difference is Acceding / Decending.

It is as it does'nt care about the FieldIndex but rather the Name.

ClinicCollection col = new ClinicCollection();

IRelationCollection relations = new RelationCollection();
relations.Add(ClinicEntity.Relations.AddressEntityUsingAddressid);
relations.Add(AddressEntity.Relations.CountryEntityUsingCountryid);

ISortExpression sorter = new SortExpression();
if (type == CLINICSEARCHSORTTYPE.NAME)
{
    sorter.Add(SortClauseFactory.Create(
        ClinicFieldIndex.Name, SortOperator.Ascending)
        );
}

if (type == CLINICSEARCHSORTTYPE.COUNTRY)
{
    sorter.Add(SortClauseFactory.Create(
        CountryFieldIndex.Name, SortOperator.Descending )
        );
}

After looking at this post it looks as though this may be an issue from SQL Server not having the latest patches. http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=330&HighLight=1 If you are not using sql server some other databases have issues with ordering by columns that are not in the resultset. If this is the case post more about your environment and we can be of more help.

Does the query produced (enable tracing to see the query) include the right field? The order by clause will contain the field using catalog.schema.object.field name naming so it can't be the sort is done differently unless indeed the servicepack issue is the one you're running into.

Hi. Just installed SP4 for SQL 2000 (wich i´m using)

The query generated is

SELECT [dbo].[CLINIC].[CLINICID] 
AS [Clinicid], [dbo].[CLINIC].[ADDRESSID] 
AS [Addressid], [dbo].[CLINIC].[NAME] 
AS [Name], [dbo].[CLINIC].[ACTIVE] 
AS [Active] 

FROM (( [dbo].[ADDRESS]  

INNER JOIN [dbo].[CLINIC]  ON  [dbo].[ADDRESS].[ADDRESSID]=[dbo].[CLINIC].[ADDRESSID]) 
INNER JOIN [dbo].[COUNTRY]  ON  [dbo].[COUNTRY].[COUNTRYID]=[dbo].[ADDRESS].[COUNTRYID]) 

WHERE ( ( [dbo].[CLINIC].[NAME] LIKE '%as%' AND [dbo].[CLINIC].[ACTIVE] = 1)) 

ORDER BY [dbo].[COUNTRY].[NAME] DESC

And sure enough the Country.Name field is not in the result set but is applied thorugh the join. When accessing the Country field on the result set I say Clinic.Country.Name (and a new fetch is done).

So what is the solution? If i add the Country.Name to query it works but how to I do that i LLBLGen? Prefetch?

[Edit: Just tried prefetch and it of course did not work since it uses seperate queries]

Thanks Anders Jacobsen

Please confirm: - if you run the query generated in query analyzer, does it report results sorted by country? - is the read entity collection sorted by country?

Be aware: the country is likely the same for a lot of clinics, this means that the order in which they're returned is undefined. If you want them to be in a given order, specify that field in the sortexpression as well.

Otis wrote:

• if you run the query generated in query analyzer, does it report results sorted by country?

It is the same problem. If i specify the Country.Name in the resultset the sorting is correct, if i ony sort on the Country.Name but I leave it out in the Resultset its not sorted on Country.Name (Tested in Query analyzer)

Otis wrote:

• is the read entity collection sorted by country?

The read collection? I use the predicateFilter on a ClinicCollection and join the address and country on. I add the sort expressen to this GetMulti so i assume that its sorted bo the country (but then again...its not) I might have misundestood you here??

The source to the problem seems not to be LLBLGen but the fact that SQL Server can´t sort on a field which is not in the result set. So would be no problem i wrote my SQL my self but it is a problem when the SQL is autogenerated.

Any suggestions?

Maybe the problem is else where. If I select @@version I get

Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

Is the Service pack 4 actually not installed? or is it refering to service pack 2 on my XP instaltion?

Anderskj1 wrote:

Maybe the problem is else where. If I select @@version I get

Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

Is the Service pack 4 actually not installed? or is it refering to service pack 2 on my XP instaltion?

SP2 on XP (which is of build 2600 ). the real important value is 2039 which is the actual version / build nr of sqlserver you're working on. This is SP4 at least so you have applied the correct sp.

SqlServer is able to sort on a column not in the resultset, though if that leads to a lot of duplicates the results you're ACTUALY fetching don't look sorted.

The collection you're fetching, are the clinics sorted by country or not? So is the sorting doing anything or not?

Similar query: select O.* from orders o inner join customers c on o.customerid = c.customerid order by c.country asc

I get the same resultset when I place the c.country in the select list or not.

I understand you don't get the same resultset?

Im pretty sure im correct. (As I have been 100 of times were I where not )

Below i show the 2 queries

Case 1 the Country.Name is IN the result field and the following lines is last as aspected.

1110526994680 Aleksander Laschke 1 Denmark 1029752788631 KAS Herlev 1 Denmark

Case 2 i have out-commented the Country.Name in the result and the resultset is as fare as I can see NOT sorted by the Country.Name but on the Clinic.Name

== 1) WITH COUNTRY IN RESULT======================

SELECT [dbo].[CLINIC].[CLINICID] AS [Clinicid], [dbo].[CLINIC].[NAME] AS [Name], [dbo].[CLINIC].[ACTIVE] AS [Active], [dbo].[COUNTRY].[NAME] As [CountryName] FROM (( [dbo].[ADDRESS]

INNER JOIN [dbo].[CLINIC] ON [dbo].[ADDRESS].[ADDRESSID]=[dbo].[CLINIC].[ADDRESSID]) INNER JOIN [dbo].[COUNTRY] ON [dbo].[COUNTRY].[COUNTRYID]=[dbo].[ADDRESS].[COUNTRYID])

WHERE ( ( [dbo].[CLINIC].[NAME] LIKE '%as%' AND [dbo].[CLINIC].[ACTIVE] = 1))

ORDER BY [dbo].[COUNTRY].[NAME] DESC

GIVES

1025699121564 University of Glasgow 1 United Kingdom 1115700688084 Yasam Hospital 1 Turkey 1096443141284 Instituto Extremeno de Reproduccion Asistida 1 Spain 1086064864131 Centre for Assisted Reproduction Ltd 1 Singapore 1117706610535 IASO Hospital, IVF Center 1 Greece 1039603149037 Felicitas-Klinikka 1 Finland 1112076151301 Nova Vita Kliinik AS 1 Estonia 1110526994680 Aleksander Laschke 1 Denmark 1029752788631 KAS Herlev 1 Denmark

== 2) WITHOUT COUNTRY IN RESULT======================

SELECT [dbo].[CLINIC].[CLINICID] AS [Clinicid], [dbo].[CLINIC].[NAME] AS [Name], [dbo].[CLINIC].[ACTIVE] AS [Active] --[dbo].[COUNTRY].[NAME] As [CountryName] FROM (( [dbo].[ADDRESS]

INNER JOIN [dbo].[CLINIC] ON [dbo].[ADDRESS].[ADDRESSID]=[dbo].[CLINIC].[ADDRESSID]) INNER JOIN [dbo].[COUNTRY] ON [dbo].[COUNTRY].[COUNTRYID]=[dbo].[ADDRESS].[COUNTRYID])

WHERE ( ( [dbo].[CLINIC].[NAME] LIKE '%as%' AND [dbo].[CLINIC].[ACTIVE] = 1))

ORDER BY [dbo].[COUNTRY].[NAME] DESC

GIVES

1115700688084 Yasam Hospital 1 1025699121564 University of Glasgow 1 1112076151301 Nova Vita Kliinik AS 1 1029752788631 KAS Herlev 1 1096443141284 Instituto Extremeno de Reproduccion Asistida 1 1117706610535 IASO Hospital, IVF Center 1 1039603149037 Felicitas-Klinikka 1 1086064864131 Centre for Assisted Reproduction Ltd 1 1110526994680 Aleksander Laschke 1

I have no idea, it indeed should be the same resultset. I'd suggest to post the same queries as you did in your last post, in the public SqlServer developer newsgroup and see which answers come up. The queries you posted seem logical enough to stand on their own and should work as expected. Unless we both overlook something of course

Thank for trying. Ill do that.

I now have an explantion for the problem described --------------------------------------------------------------

Credits to: Hugo Kornelis, SQL Server MVP

The reason is the somewhat wacky way in which a reference in the ORDER BY list is used. There are no less than four ways to tell SQL Server how to sort the results:

The first two are defined in the ANSI standard:

1. By ordinal position: "ORDER BY 2" means: order by the 2nd column in the SELECT list,

2. By the alias of an expression in the SELECT list: SELECT Column1 AS Yippee, ... ... ORDER BY Yippee

The last two are non-ANSI-standard syntax extensions:

1. By an expression: "ORDER BY Column1 * Column2 - Column3". The expression cannot reference aliases or ordinal positions of columns in the SELECT list; it can, however, reference all columns of all tables in the FROM clause.

2. By column name (this is a special case of #2 if the column name is used in the WHERE clause without alias -i.e. the alias is equal to the name of the column-, and a special case of #3 otherwise).

Number 4 is where the problems start. For instance, how should this query be ordered:

SELECT X as Y, Y as X FROM SomeTable ORDER BY X

Is X a reference to the column SomeTable.X (aliased as Y in the result) or to the alias X (which is assigned to SomeTable.Y)?

Of course, if we had used "ORDER BY SomeTable.X", there would have been no ambiguity - and that is exactly where SQL Server 2000 displays the wacky behaviour I mentioned before. In some casees, SQL Server 2000 will completely disregard the prefixed table, use only the column name in the ORDER BY clause - and interpret it according to rule #2 above!

2) WITHOUT COUNTRY IN RESULT======================

SELECT [dbo].[CLINIC].[CLINICID] AS [Clinicid], [dbo].[CLINIC].[NAME] AS [Name], [dbo].[CLINIC].[ACTIVE] AS [Active] --[dbo].[COUNTRY].[NAME] As [CountryName] (snip) ORDER BY [dbo].[COUNTRY].[NAME] DESC

This is, apparently, such a case. SQL Server disregards dbo.Country, is left with only NAME and matches that to the column alias used for Clinic.Name.

Some workarounds are:

Change the alias for Clinic.Name: [dbo].[CLINIC].[NAME] AS [ClinicName],

Include a dummy operation to force SQL Server to interpret the ORDER BY argument as expression: ORDER BY [dbo].[COUNTRY].[NAME] + '' DESC

Upgrade to SQL Server 2005 - I've read (somewhere - I've been hunting for it but can't find it now) that SQL Server 2005 will always treat a qualified name in the ORDER BY clause as a column name.

Now my qustion is. How do I handle this in my LLBLGen code? Can i somehow add an Alias on the Clinic.Name so the will be no conflict in the query generated from LLBLGen?

The simplest solution if you may approve it, is to change the field name in the database. !!

You may also use a dynamic list instead of en EntityCollection, where you can set aliases for your fields. That might be a work-around.