Home » Developer & Programmer » Precompilers, OCI & OCCI » query limitation?
query limitation? [message #435085] Mon, 14 December 2009 04:14 Go to next message
mitemb
Messages: 1
Registered: December 2009
Junior Member
Dear all,

I have a problem using the occi call interface with a query which works in interactive mode but fails using the interface. I suspect that this is a limiation in the interface. The query has the follwing structure:
select distinct
       table1.x
       table2.a
          .
          .
          .
       table2.q
from
table2 join (
select max(time) as max_date from
table2 join
table1 on
          table1.id=table2.id
where
      time > timestamp1 AND
      time < timestamp2 AND
      [...] AND
      [...] group by table1.a,table2.id order by table1.a ) b
on table2.timestamp=b.max_date join table1 on
 table1.id=table2.id
where
      time > timestamp1 AND
      time < timestamp2 AND
      [...] AND
      [...]



The query fails with a
Quote:

Unable to use database: ORA-32109: invalid column or parameter position

which I suspect has to do with the nested select.

Am I missing something obvious here, or is there a general problem with the kind of query using the occi?

Any suggestions appreciated.

Cheers

P.S. I suspect that also the query might not be optimal, but it does what it is supposed to, and the optimization would a question for a different forum, so.....
Re: query limitation? [message #435105 is a reply to message #435085] Mon, 14 December 2009 05:58 Go to previous message
Littlefoot
Messages: 20827
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Could you rewrite it? Perhaps OCCI doesn't like ANSI joins? Something like
select ...
from table1 t1, table2 t2
where t1.id = t2.id
  and t2.timestamp = (select max(t11.time_stamp) 
                      from table2 t21
                      where t21.id = t2.id
                        and ...
                     )
  and ...
Previous Topic: Connection Pooling over Multiple processes
Next Topic: PCC-F-02102, Fatal error while doing C preprocessing
Goto Forum:
  


Current Time: Tue Sep 27 09:24:09 CDT 2016

Total time taken to generate the page: 0.12239 seconds