Old Style SQL Vs. ANSI SQL...

srkpriv · **Joined:** Tue Nov 30, 2004 2:37 pm **Posts:** 16

I am Using Hibernate 3.x and Oracle 9.x.

I have this issue when I convert from native sql to ansi sql, some rows are not returned due to outer join differences. My original query in native SQL contains something like this...

tableAlias.ColumnName (+) = 1

This above table participates in outer join with other tables, when I convert this query to ANSI SQL

something like this;

Left Outer Join Tablename TableAlias on TableAlias.columnName = TableAlias.columnName

The problem is I dont know the equivalent if avilable for

tableAlias.ColumnName (+) = 1 to ANSI standard.

If I use the above condition directly, some rows are not returned, which were originally returned in the old style SQL.

Please help if someone knows a solution for this... Thanks.

sven · **Posted:** Wed Aug 31, 2005 12:23 pm

I think it should just be:

Code:

LEFT OUTER JOIN Tablename TableAlias ON (TableAlias.columnName = TableAlias.columnName AND TableAlias.columnName = 1)

This is an additional condition in the ON clause meaning that the candidate row is to be returned, if TableAlias.columnName is NULL or equals 1.

FYI: Using an additional condition within HQL you can use the new WITH clause (Hibernate 3.1 beta 2).

Best regards
Sven

srkpriv · **Joined:** Tue Nov 30, 2004 2:37 pm **Posts:** 16

Thanks a lot, that worked!.

srkpriv · **Joined:** Tue Nov 30, 2004 2:37 pm **Posts:** 16

Sven, this feature is not available in 3.0 is that what you are saying? I was hoping your solution would be availble in 3.0.5 hibernate version.

Please confirm?

Thanks a lot.

sven · **Posted:** Wed Aug 31, 2005 12:50 pm

The HQL WITH clause, which adds "extra" restrictions to an association join, is available for Hibernate 3.1. I think you'll have to use at least beta 2.

An alternative way to do it with HQL (e.g. version 3.0):

Code:

select c1 from Clazz1 as c1
left join c1.c2 as joinedClass
where joinedClass.column is null or joinedClass.column = 1

That's not quite elegant, but it works and AFAIK there's no other solution for adding "extra" conditions to an association join with Hibernate prior to 3.1.

Best regards
Sven

srkpriv · **Joined:** Tue Nov 30, 2004 2:37 pm **Posts:** 16

Yes I tried that solution, but I want the one equivalen to (+), anyway, your first solution was good, I used it to write a ANSI style native SQL.. when we upgrade to hibernate 3.1 etc.. we would implement this (if the client wants).. anyway, this is good information.
thanks...

rich · **Joined:** Sat Dec 20, 2003 5:09 pm **Posts:** 38

Sorry for butting in here, but I'm curious. With Hibernate 3.1 beta 2 I'm trying to do something like this:

Code:

    <query name="DashConfigQry">
        <![CDATA[
             from DashConfig As C
                left join fetch C.selectBoxItems as ITEMS
                left join fetch C.dashTabData as DATA with DATA.clientId = :CLIENT_ID 
                left join fetch C.dashNotes as NOTES with NOTES.clientId = :CLIENT_ID 
             order by C.viewId, C.displayOrder, ITEMS.item
        ]]>
    </query> 

And what I get in the logs is this:

Code:

Caused by: with-clause not allowed on fetched associations; use filters

Is there no way to prefetch data using the with clause without having to use filters?