This is a migrated thread and some comments may be shown as answers.

OQL query with implicit DISTINCT

5 Answers 339 Views

OQL (OQL specific questions)

This is a migrated thread and some comments may be shown as answers.

This question is locked. New answers and comments are not allowed.

5 Answers, 1 is accepted

answered on 13 Mar 2013, 12:25 PM

Hi Thomas,

here are the sources, I hope, you can help me!

OQL source:

SELECT

Person.PersId,

Employee.EmplId,

Person.Name,

Person.PersCode,

Employee.HireDate,

Employee.TermDate,

Employee.LastWorkedDate,

EmplHist.DivSelId,

Employee.CalcOrder

FROM

AbPersonExtent as Person,

Person.AbPersComps as PersComp,

Person.AbEmpls as Employee,

Employee.AbEmplHists as EmplHist,

AbDivSelExtent as EmplHistDivSel

WHERE

Person.Deleted = '0' AND

Person.BirthDate >= $1 AND

Person.BirthDate <= $2 AND

PersComp.CompId = $3 AND

Exists PersType IN Person.AbPersTypes : ( (PersType.PersType = 'PersType|Empl') AND

PersType.Deleted = '0' ) AND

Employee.Deleted = '0' AND

EmplHist.Deleted = '0' AND

Employee.HireDate >= $4 AND

Employee.HireDate <= $5 AND

Employee.TermDate >= $6 AND

Employee.TermDate <= $7 AND

Employee.LastWorkedDate >= $8 AND

Employee.LastWorkedDate <= $9 AND

EmplHist.StartDate <= $10 AND

EmplHist.EndDate >= $10 AND

EmplHistDivSel.Deleted = '0' AND

EmplHist.DivSelId = EmplHistDivSel.DivSelId

Generated query:

SELECT DISTINCT

a.[PersId] AS COL1,

c.[EmplId] AS COL2,

a.[Name] AS COL3,

a.[PersCode] AS COL4,

c.[HireDate] AS COL5,

c.[TermDate] AS COL6,

c.[LastWorkedDate] AS COL7,

d.[DivSelId] AS COL8,

c.[CalcOrder] AS COL9

FROM

[AbPerson] a JOIN [AbPersComp] AS b ON (a.[PersId] = b.[PersId])

JOIN [AbEmpl] AS c ON (a.[PersId] = c.[PersId])

JOIN [AbEmplHist] AS d ON (c.[EmplId] = d.[EmplId])

JOIN [AbPersType] AS e ON (a.[PersId] = e.[PersId])

JOIN [AbDivSel] AS f ON (a.[Deleted] = '0' AND

a.[BirthDate] >= ? AND

a.[BirthDate] <= ? AND

b.[CompId] = ? AND

e.[PersType] = 'PersType|Empl' AND

e.[Deleted] = '0' AND

c.[Deleted] = '0' AND

d.[Deleted] = '0' AND

c.[HireDate] >= ? AND

c.[HireDate] <= ? AND

c.[TermDate] >= ? AND

c.[TermDate] <= ? AND

c.[LastWorkedDate] >= ? AND

c.[LastWorkedDate] <= ? AND

d.[StartDate] <= ? AND

d.[EndDate] >= ? AND

f.[Deleted] = '0' AND

d.[DivSelId] = f.[DivSelId])

Regards,

Peter Szintai

answered on 18 Mar 2013, 12:26 PM

Hi Peter,

It's my fault that I did not request the respective parts of your domain model as well. I thought I would not need it, but it seems not so easy to reproduce your issue. I tried with a Northwind model here on my side, but was not able to do so.
Would you be so kind and give us the definitions of the needed classes for the query? Also, am I correct to assume that you use the ObjectScope API, not the OpenAccessContext API? Do you have a FetchPlan set?

All the best,
Thomas
the Telerik team

OpenAccess ORM Q1 2013 is out featuring Multi-Diagrams, Persistent Data Stream Support and much more. Check out all of the latest highlights.