Hi everyone,
I'm having a wierd problem with a select statement in OpenBD
connecting to an Oracle 10gR2 DB.
The actual statement is:
select to_char(cobjekt) as cobjekt from os_kopf k where k.RCPHASE in
('AEBO00000','AHBOXTR00','ANBOXTR03','MEPO00000','HXDVETR10') union
select to_char(k.iauftrag) as iauftrag from os_kopf k, bo_auftrag b
where k.iauftrag=b.iauftrag and k.RCPHASE in
('AEBO00000','AHBOXTR00','ANBOXTR03','MEPO00000') and k.dchange <
sysdate-1/1440 union select to_char(k.iauftrag) as iauftrag from
os_kopf k, bo_auftrag b, os_tradingplaceese e where
k.iauftrag=b.iauftrag and b.CBROKER=e.cbroker and RCPHASE in
('AEBO00000','AHBOXTR00','ANBOXTR03','MEPO00000','AHBOESE01') and
k.dchange < sysdate-1/1440 and b.CBOERSE = e.CCODE and
k.CDESTINATION=e.CDESTINATION and DOPENINGTIME < sysdate-2/1440 union
select to_char(k.iauftrag) as iauftrag from os_kopf k, bo_auftrag b
where k.iauftrag=b.iauftrag and k.RCPHASE in
('AEBO00000','AHBOXTR00','ANBOXTR03','MEPO00000','AHBOESE01') and
(b.fboanzahl - b.fboplazierungsumme) > 0;
The error I'm getting is an ORA-907: missing right parenthesis
This statement is working just fine in SQL*plus and in Toad. I also
checked it in SQL-Developer to confirm if it was a jdbc or sqlnet
problem and it worked also there.
What I did discover was that in my "IN ( )" clauses if I had double
single quotes I got the same error messege in SQL Developer.
This is also what OpenBD returns in the error page
Native Error Code 907
SQL State 42000
SQL select to_char(cobjekt) as cobjekt from os_kopf k where k.RCPHASE
in
(''AEBO00000'',''AHBOXTR00'',''ANBOXTR03'',''MEPO00000'',''HXDVETR10'')
union select to_char(k.iauftrag) as iauftrag from os_kopf k,
bo_auftrag b where k.iauftrag=b.iauftrag and k.RCPHASE in
(''AEBO00000'',''AHBOXTR00'',''ANBOXTR03'',''MEPO00000'') and
k.dchange < sysdate-1/1440 union select to_char(k.iauftrag) as
iauftrag from os_kopf k, bo_auftrag b, os_tradingplaceese e where
k.iauftrag=b.iauftrag and b.CBROKER=e.cbroker and RCPHASE in
(''AEBO00000'',''AHBOXTR00'',''ANBOXTR03'',''MEPO00000'',''AHBOESE01'')
and k.dchange < sysdate-1/1440 and b.CBOERSE = e.CCODE and
k.CDESTINATION=e.CDESTINATION and DOPENINGTIME < sysdate-2/1440 union
select to_char(k.iauftrag) as iauftrag from os_kopf k, bo_auftrag b
where k.iauftrag=b.iauftrag and k.RCPHASE in
(''AEBO00000'',''AHBOXTR00'',''ANBOXTR03'',''MEPO00000'',''AHBOESE01'')
and (b.fboanzahl - b.fboplazierungsumme) > 0
All my single quotes is replaced by doubble single quotes.
So... my question is... Is OpenBD doing this also in the CFQUERY or
just in the error message?
I'm on a OpenBD 1.5 Nightly Build from 16th of December 2010
Thanks in Advance
Mats
--
Open BlueDragon Public Mailing List
http://www.openbluedragon.org/ http://twitter.com/OpenBlueDragon
official manual: http://www.openbluedragon.org/manual/
Ready2Run CFML http://www.openbluedragon.org/openbdjam/
mailing list - http://groups.google.com/group/openbd?hl=en
