Hi,

Yesterday I was writing a query which used an innerjoin on two tables. I properly defined the aliases on both my queries, but on running the query I kept on getting an Oracle error that a column was ambiguously defined.

Looking at the actual query that ES outputs to Oracle I saw that the join aliases are not being put in front of the column names on the order by statements, causing the order by ID to throw an error, since I've got an ID column in both tables.

See code:

1                Dim NewsItems As New NewsTranslationQuery("i")
2                Dim MyNewsQuery As New NewsQuery("n")
3    
4                MyNewsQuery.Select(MyNewsQuery, NewsItems.Title.As("NewsItemTitle"))
5                MyNewsQuery.InnerJoin(NewsItems).On(MyNewsQuery.Id = NewsItems.IdNews)
6                MyNewsQuery.Where(MyNewsQuery.Datedisplay.LessThanOrEqual(Now()))
7                MyNewsQuery.Where(MyNewsQuery.Dateexpiration.GreaterThanOrEqual(Now()))
8                MyNewsQuery.Where(MyNewsQuery.IdCategory.Equal(Category))
9                MyNewsQuery.Where(MyNewsQuery.Ndelaygroup.LessThanOrEqual(Level) Or ((MyNewsQuery.Datedisplay + MyNewsQuery.Delay).LessThanOrEqual(Now()) And MyNewsQuery.Dateexpiration.GreaterThanOrEqual(Now()) And MyNewsQuery.Delay <> 9))
10               MyNewsQuery.es.Top() = 10
11               MyNewsQuery.OrderBy(MyNewsQuery.Datedisplay, esOrderByDirection.Descending)
12               MyNewsQuery.OrderBy(MyNewsQuery.Id, esOrderByDirection.Descending)
13               Return Me.Load(MyNewsQuery)

This is what it outputs:

SELECT n.*,i."TITLE" AS "NewsItemTitle" FROM "INTRANET"."NEWS" n INNER JOIN "INTRANET"."NEWS_TRANSLATION" i ON n."ID" = i."ID_NEWS" WHERE n."DATEDISPLAY" <= :DATEDISPLAY1 AND n."DATEEXPIRATION" >= :DATEEXPIRATION2 AND n."ID_CATEGORY" = :ID_CATEGORY3 AND (n."NDELAYGROUP" <= :NDELAYGROUP4 OR ((n."DATEDISPLAY" <= :DATEDISPLAY5 AND n."DATEEXPIRATION" >= :DATEEXPIRATION6 ) AND n."DELAY" <> :DELAY7 )) ORDER BY "DATEDISPLAY" DESC,"ID" DESC

As you can see there are no aliases in front of the order by column names.

I would really like to sort on Id as well. Is there a solution to this problem? Am I doing something wrong here or is this a bug in ES?

Any help would be greatly appreciated!

We'll have to get a fix out for that. You can use the in-line raw SQL technique as a work-around.

MyNewsQuery.OrderBy("<n.""ID"">", esOrderByDirection.Descending)