query limitation? [message #435085] |
Mon, 14 December 2009 04:14 |
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 |
|
Littlefoot
Messages: 21821 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 ...
|
|
|