# ActiveRecord, Oracle oci8 and LEFT OUTER JOIN

**URL:** <https://www.ruby-forum.com/t/activerecord-oracle-oci8-and-left-outer-join/89505>\
**Category:** Ruby\
**Created:** [March 20, 2007, 10:06pm UTC](https://www.ruby-forum.com/t/activerecord-oracle-oci8-and-left-outer-join/89505 "2007-03-20T22:06:48Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![stickstone](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/6f9a4e/32.png) [@stickstone](https://www.ruby-forum.com/u/stickstone)\
**Post date:** [March 20, 2007, 10:06pm UTC](https://www.ruby-forum.com/t/activerecord-oracle-oci8-and-left-outer-join/89505/1 "2007-03-20T22:06:48Z")

</div>

Hello,

This is an ActiveRecord question / issue. I hope it’s OK to raise it  
here(\*)

I’m using activerecord-1.15.2 as part of a Rails app. I started  
development  
using Sqlite3 as the backend for expediency (laptop programming on a  
plane!). I’m now trying to move it to Oracle 10.2. Unfortunately, the  
eager  
loading of related objects using :include seems to generate SQL which  
Oracle  
barfs on.

## The error message I get is:

Showing app/views/entities/show.rhtml where line #1 raised:

## OCIError: ORA-00920: invalid relational operator: SELECT entities.id AS t0\_r0, entities.key\_attribute\_id AS t0\_r1, entities.key\_value AS t0\_r2, entities.state AS t0\_r3, avpairs.id AS t1\_r0, avpairs.entity\_id AS t1\_r1, avpairs.attribute\_id AS t1\_r2, avpairs.value AS t1\_r3 FROM entities LEFT OUTER JOIN avpairs ON avpairs.entity\_id = entities.id WHERE (0 OR (entities.key\_attribute\_id=2 AND entities.key\_value=‘vpn1s4’) OR (entities.key\_attribute\_id=3 AND entities.key\_value=‘000001’))

And the source which generates this query:

```
cond = ["0"]
links.each do |pri, key_attribute_id, key_value|
  cond.first << " OR (entities.key_attribute_id=? AND 

```

entities.key\_value=?)"  
cond \<\< key\_attribute\_id \<\< key\_value  
end  
entities = Entity.find(:all, :conditions =\> cond, :include =\>  
:avpairs)

The ‘links’ array includes a list of keys to Entity objects that I wish  
to  
load, together with their linked Avpair objects. This all works just  
dandy  
under Sqlite3.

Does this mean that AR eager loading can’t work at all with Oracle? Or  
is  
there something specific about this particular query which is causing  
the  
problem?

In the “Agile” book I see a footnote on page 362:  
“In fact, it might not work at all! If your database doesn’t support  
left outer joins, you can’t use the feature. Oracle 8 users, for  
instance,  
will need to upgrade to version 9 to use preloading.”

But then I’d have thought I’d be fine with Oracle 10.2

The Oracle SQL reference starts at  
[http://download-uk.oracle.com/docs/cd/B19306\_01/server.102/b14200/toc.htm](http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/toc.htm)  
and the documentation for SELECT is at  
[http://download-uk.oracle.com/docs/cd/B19306\_01/server.102/b14200/statements\_10002.htm#i2065646](http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10002.htm#i2065646)

It shows:

join\_clause ::= table-reference (inner | outer\_join\_clause)+

outer\_join\_clause ::= … outer\_join\_type JOIN table\_reference ON  
condition

outer\_join\_type ::= (FULL|LEFT|RIGHT) OUTER

so I can’t actually see why it would reject the SQL generated by AR. Any  
ideas?

Regards,

Brian.

(\*) At [http://ar.rubyonrails.com/](http://ar.rubyonrails.com/) it does say:

“For other information, feel free to ask on the ruby-talk mailing list  
(which is mirrored to comp.lang.ruby) or contact removed\_email\_address@domain.invalid”

However if there’s a more appropriate ActiveRecord mailing list please  
point  
me at it. There are a couple of other minor AR issues/suggestions I’d  
like  
to raise too.

---

<div class="post-metadata">

**Author:** ![stickstone](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/6f9a4e/32.png) [@stickstone](https://www.ruby-forum.com/u/stickstone)\
**Post date:** [March 20, 2007, 10:10pm UTC](https://www.ruby-forum.com/t/activerecord-oracle-oci8-and-left-outer-join/89505/2 "2007-03-20T22:10:50Z")

</div>

> OCIError: ORA-00920: invalid relational operator: SELECT entities.id AS  
> t0\_r0, entities.key\_attribute\_id AS t0\_r1, entities.key\_value AS t0\_r2,  
> entities.state AS t0\_r3, avpairs.id AS t1\_r0, avpairs.entity\_id AS t1\_r1,  
> avpairs.attribute\_id AS t1\_r2, avpairs.value AS t1\_r3 FROM entities LEFT  
> OUTER JOIN avpairs ON avpairs.entity\_id = entities.id WHERE (0 OR  
> (entities.key\_attribute\_id=2 AND entities.key\_value=‘vpn1s4’) OR  
> (entities.key\_attribute\_id=3 AND entities.key\_value=‘000001’))

Doh! Running the query under yasql shows the error to be at  
WHERE (0 OR …  
and removing it makes the problem goes away. And this was really just  
laziness on my part in formulating the SQL statement builder.

Sorry for the noise.

Regards,

Brian.