Hello,
we are facing issues in executing the HQL query with LeftOuterjoin fetch , while exceuting the query it is appending the leftouter join conditions for the properties which exists with OR condition in the where clause, at this moment it is throwing an Exception saying that 'ORA-01719: outer join operator (+) not allowed in operand of OR or IN'.
The query should look like this:
FROM DDR_TARGETED_DRG_LIST ddrtargete0_,
DRG drgvalue1_, CODE codevalue2_, DRG drgvalue3_,
CODE codevalue4_, DRG drgvalue5_, CODE codevalue6_
WHERE ddrtargete0_.PRIMARY_DRG_ID=drgvalue1_.DRG_ID(+) AND ddrtargete0_.PRIMARY_DRG_ID=drgvalue1_.DRG_ID(+) AND drgvalue1_.MDC_CODE_ID=codevalue2_.CODE_ID(+) AND ddrtargete0_.DRG2_ID=drgvalue3_.DRG_ID(+) AND ddrtargete0_.DRG2_ID=drgvalue3_.DRG_ID(+) AND drgvalue3_.MDC_CODE_ID=codevalue4_.CODE_ID(+) AND ddrtargete0_.DRG3_ID=drgvalue5_.DRG_ID(+) AND ddrtargete0_.DRG3_ID=drgvalue5_.DRG_ID(+) AND drgvalue5_.MDC_CODE_ID=codevalue6_.CODE_ID(+) AND (drgvalue1_.DRG_CODE =014 or drgvalue3_.DRG_CODE =014 or drgvalue5_.DRG_CODE =014) AND 1=1 ORDER BY DRG_CODE
but in Hibernate , when we add a Criteria with 'LeftOuterjoin' , it is generating the query as below:
Select * from DDR_TARGETED_DRG_LIST ddrtargete0_, DRG drgvalue1_, CODE codevalue2_, DRG drgvalue3_, CODE codevalue4_, DRG drgvalue5_, CODE codevalue6_ where ddrtargete0_.PRIMARY_DRG_ID=drgvalue1_.DRG_ID(+) and ddrtargete0_.PRIMARY_DRG_ID=drgvalue1_.DRG_ID(+) and drgvalue1_.MDC_CODE_ID=codevalue2_.CODE_ID(+) and ddrtargete0_.DRG2_ID=drgvalue3_.DRG_ID(+) and ddrtargete0_.DRG2_ID=drgvalue3_.DRG_ID(+) and drgvalue3_.MDC_CODE_ID=codevalue4_.CODE_ID(+) and ddrtargete0_.DRG3_ID=drgvalue5_.DRG_ID(+) and ddrtargete0_.DRG3_ID=drgvalue5_.DRG_ID(+) and drgvalue5_.MDC_CODE_ID=codevalue6_.CODE_ID(+) and ((drgvalue1_.DRG_CODE=? and <i>ddrtargete0_.PRIMARY_DRG_ID=drgvalue1_.DRG_ID(+)</i>
OR(drgvalue3_.DRG_CODE=? and <i>ddrtargete0_.DRG2_ID=drgvalue3_.DRG_ID(+)</i>
OR(drgvalue5_.DRG_CODE=? <i>and ddrtargete0_.DRG3_ID=drgvalue5_.DRG_ID(+)</i>
AND(1=1 )) order by ddrtargete0_.DDR_TARGETED_DRG_LIST_ID ASC
please let me know , how to avoid appending this leftouter join condition in the where clause with each property having OR condition.
thanks in advance.