case statement with multiple joins to the same table

dlgrasse · **Joined:** Thu Feb 08, 2007 7:53 pm **Posts:** 5

The situation I'm seeing is that when a class has a many-to-one mapping to another table multiple times, that the HQL case statement can generate improper SQL.

More specifically to my context, there is an Entity class that has FK's to a Contact table for each type of contact. The Contact class has a nullable FK to an SMSProvider class, which holds carrier/domain mappings. In my query I need to pull the 'number@domain' concatenated value, but only if the FK is no null. Otherwise just return an empty string.

Here are some env details:

And here is the HQL I'm creating and the corresponding SQL...see how the SMSProvider table references in the where clause is placed in between the two inner join clauses...

HQL:

Code:

select
  case when c1.SMSProvider is not null then concat(c1.SMSNumber,'@',c1.SMSProvider.domain) else '' end,
  case when c2.SMSProvider is not null then concat(c2.SMSNumber,'@',c2.SMSProvider.domain) else '' end
from Entity e
  join e.contact1 as c1
  join e.contact2 as c2
where e.id in (1)

SQL:

Code:

select
    case 
        when contact1_.SMS_PROVIDER_ID is not null then contact1_.SMSNum||'@'||smsprovide3_.SMS_PROVIDER_DOMAIN 
        else '' 
    end as col_0_0_,
    case 
        when contact2_.SMS_PROVIDER_ID is not null then contact2_.SMSNum||'@'||smsprovide4_.SMS_PROVIDER_DOMAIN 
        else '' 
    end as col_1_0_ 
from ENTITY entity0_ 
    inner join
        CONTACT contact1_ 
            on entity0_.CONTACT1_ID=contact1_.CONTACT_KEY,
        SMSPROVIDER smsprovide3_ 
    inner join
        CONTACT contact2_ 
            on entity0_.CONTACT2_ID=contact2_.CONTACT_KEY,
        SMSPROVIDER smsprovide4_ 
where contact2_.SMS_PROVIDER_ID=smsprovide4_.SMS_PROVIDER_KEY 
    and contact1_.SMS_PROVIDER_ID=smsprovide3_.SMS_PROVIDER_KEY 
    and (
        entity0_.ENTITY_KEY in (
            1
        )
    )

Here is the exact error I'm getting:

Code:

ORA-00904: "entity0_"."CONTACT2_ID": invalid identifier

Any thoughts on this? A better way to structure the HQL? Is this simply a bug?

dlgrasse

dharmendra.pandey · **Posted:** Fri Feb 09, 2007 12:40 am

Hi dlgrasse,

CONTACT2_ID not belong to ENTITY table

dlgrasse · **Joined:** Thu Feb 08, 2007 7:53 pm **Posts:** 5

dharmendra.pandey wrote:

CONTACT2_ID not belong to ENTITY table

Looking at the Oracle error, that would be the immediate conclusion. However that is not the problem. As an example, here is the same query without the first case statement (leaving only the CONTACT2_ID part) that runs fine...

Code:

select 
    case 
        when contact2_.SMS_PROVIDER_ID is not null then contact2_.SMSNum||'@'||smsprovide4_.SMS_PROVIDER_DOMAIN 
        else '' 
    end as col_1_0_ 
from ENTITY entity0_ 
    inner join 
        CONTACT contact2_ 
            on entity0_.CONTACT2_ID=contact2_.CONTACT_KEY, 
        SMSPROVIDER smsprovide4_ 
where contact2_.SMS_PROVIDER_ID=smsprovide4_.SMS_PROVIDER_KEY 
    and ( 
        entity0_.ENTITY_KEY in ( 
            1 
        ) 
    )

likewise, if the original SQL has the first SMSProvider reference in the where clause moved to a correct location, it runs perfectly...

Code:

select 
    case 
        when contact1_.SMS_PROVIDER_ID is not null then contact1_.SMSNum||'@'||smsprovide3_.SMS_PROVIDER_DOMAIN 
        else '' 
    end as col_0_0_, 
    case 
        when contact2_.SMS_PROVIDER_ID is not null then contact2_.SMSNum||'@'||smsprovide4_.SMS_PROVIDER_DOMAIN 
        else '' 
    end as col_1_0_ 
from ENTITY entity0_ 
    inner join 
        CONTACT contact1_ 
            on entity0_.CONTACT1_ID=contact1_.CONTACT_KEY
    inner join 
        CONTACT contact2_ 
            on entity0_.CONTACT2_ID=contact2_.CONTACT_KEY, 
        SMSPROVIDER smsprovide4_ , 
        SMSPROVIDER smsprovide3_ 

where contact2_.SMS_PROVIDER_ID=smsprovide4_.SMS_PROVIDER_KEY 
    and contact1_.SMS_PROVIDER_ID=smsprovide3_.SMS_PROVIDER_KEY 
    and ( 
        entity0_.ENTITY_KEY in ( 
            1 
        ) 
    )

so the issue truly is an error in the generated SQL.

dlgrasse

dlgrasse · **Joined:** Thu Feb 08, 2007 7:53 pm **Posts:** 5

For the interested reader, the workaround is to explicitly join on the SMSProvider tables as such:

Code:

select
  case when c1sms is not null then concat(c1.SMSNumber,'@',c1sms.domain) else '' end,
  case when c2sms is not null then concat(c2.SMSNumber,'@',c2sms.domain) else '' end
from Entity e
  join e.contact1 as c1
  join c1.SMSProvicer as c1sms
  join e.contact2 as c2
  join c2.SMSProvider as c2sms
where e.id in (1)

This creates join constructs in the SQL for the SMSProvider tables instead of simply listing them as 'from' tables and doing the join in the 'where' clause.

I do think that the original construct should be accounted for though.

dlgrasse