Hi there.

I have a very strange resultset error. Im am executing a select statement agains my database. The result set should retrun 38 records and it does. No problem there. the problem is that the data returned isnt correct.

For example qhen the query is run against the table I should be retruned something like the following

ID | Country
1 US
1 UK
1 AU
2 UK
2 AU
2 BR

However Im retruned

ID | Country
1 US
1 US
1 US
2 UK
2 UK
2 UK

Has anyone come accross an error such as this. I hope this makes sense as its quite hard to put in to words.


Thanks Chris

3.1.3


<hibernate-mapping package="com.pctfiler.workflow.events">

<class name="Events">
<id name="ipAppId" type="string" />
<property name="country" type="string" />
</class>

<sql-query name="GetEvents" cacheable="true">
<return alias="event" class="Events">
<return-property name="ipAppId" column="IPAppId" />
<return-property name="country" column="country" />
</return>
SELECT i.IPAppID, i.IPAppNumber, c.Country, i.CloseDate + INTERVAL Deadline MONTH AS CountryCloseDate, i.NumWordsClaims + i.NumWordsDesc AS TotalWords, tt.TransactionType, et.EventType FROM ip_applications i INNER JOIN opportunities t ON i.IPAppID
= t.IPAppID INNER JOIN transaction_types tt ON t.OppType = tt.TransactionTypeID INNER JOIN opp_prod_countries tc ON t.OppID = tc.OppID INNER JOIN pct_countries pc ON tc.ProductID = pc.ProductID AND tc.CountryID = pc.CountryID INNER JOIN countries c
ON pc.CountryID = c.CountryID INNER JOIN events e ON tc.ProductID = e.ProductID AND tc.CountryID = e.CountryID INNER JOIN event_types et ON e.EventTypeID = et.EventTypeID ORDER BY i.IpAppID
</sql-query>

</hibernate-mapping>



My SQL 5.0

Whatever it is, it's not an hibernate issue. Hibernate doesn't mess with your SQL queries. Run the query directly in your DB client, and try removing joins and returns until the error becomes apparent.

The sql ran direclty against the database returns the corrects resultset.

Within the HIbernate environment even a very simple fo the query without joins returns a similar (erronous) recordset when rran in the mapping shown in the original post.

If I remove all of the returns from the mapping then hibernate returns a 2d array containing the correct information.

Does anyone have any idea as to why this maybe?

I have seen this kind of thing before, but I've forgotten what I did to fix it. Does adding "distinct" help? As it stands, there's nothing in the query to stop the same entity being returned multiple times, so you will have to expect that.

Thanks for the reply

Ive tried the distinct keyword but it doesnt do anything.

The rows are already distict in the database. Is there a problem withteh retrun property? AS I say if I remove this then I get the correct (raw) data without the correct object mapping?

I haven't encountered an issue, but then, I've never used so many joins to get two columns out of the DB.

Have you tried using {} syntax instead of return-property? It won't work if you have multi-column properties, but if you have only single-column properties, it is (imo) the better option. It's more readable, I think.

Thanks

Ill give it a go

If anyone is interested...

The problem was with the ids of the return object. It required a composite id instead of a singular id. Silly really should have spotted this earlier.

Thanks for your help tenwit

Chris