Hello,

I have a problem with a query inside my code.


Here is my query code :

[color=blue] StringBuffer s = new StringBuffer("select distinct odc from OdBO odc left join fetch odc.fare as fare ");
s.append(" where " ) ;
s.append(" odc.origin = '" + origin + "'");
s.append(" AND odc.destination = '" + destination + "'");
s.append(" AND odc.dir = '" + (roundTrip.booleanValue() ? TTCConstants.DIR_RT : TTCConstants.DIR_OW) + "'");
StringBuffer s = new StringBuffer("select distinct odc from OdBO odc left join fetch odc.fare as fare ");
s.append(" where " ) ;
s.append(" odc.origin = '" + origin + "'");
s.append(" AND odc.destination = '" + destination + "'");
s.append(" AND odc.dir = '" + (roundTrip.booleanValue() ? TTCConstants.DIR_RT : TTCConstants.DIR_OW) + "'");
s.append(" AND odc.status = '" + validStatus + "'");
s.append(" AND odc.snapshotDate >= :dateMin");
s.append(" AND odc.snapshotDate <= :dateMax");
s.append(" AND fare.class = FareQL2BO "); s.append(" AND odc.status = '" + validStatus + "'");
s.append(" AND odc.snapshotDate >= :dateMin");
s.append(" AND odc.snapshotDate <= :dateMax");
s.append(" AND fare.class = FareQL2BO ");[/color]


This query returns a list of records without taking into account the "Distinct" . If I execute the same query under oracle I got the correct number of records

The [color=red]OdBO[/color] object contains a [color=red]fare[/color] attribute which a list of [color=red]FareBO[/color] object and [color=red]FareQL2BO[/color] object inherits from [color=red]FareBO[/color] object. With this query, I want all the [color=red]OdBO[/color] having the selected criteria and with the associated fare of type [color=red]FareQL2BO[/color]

I got 55 [color=red]FareQL2BO[/color] but 5 distinct [color=red]OdBO[/color] and the result is 55 instaed of 5.


Anyone can help me ?

Thanks!

Cyril
[color=red][/color]

Criteria q = sessionHibernate.getSession().createCriteria(OdBO.class, "odc")
.createAlias("odc.fare", "fare")
.add(Property.forName("odc.origin").eq(origin))
.add(Property.forName("odc.destination").eq(destination))
.add(Property.forName("odc.dir").eq(roundTrip.booleanValue() ? TTCConstants.DIR_RT : TTCConstants.DIR_OW))
.add(Property.forName("odc.status").eq(validStatus))
.add(Expression.between("odc.snapshotDate", DateHelper.toMinimumDate(snapshotQL2), DateHelper.toMaximumDate(snapshotQL2)))
.add(Property.forName("fare.class").eq(FareQL2BO.class))
.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);