Can't really see anything wrong, but if you have a ";" at the end of your SQL then remove it. Else post your CFML code.
Kind Regards, Nitai On Mon, Dec 27, 2010 at 9:39 AM, Mats <[email protected]> wrote: > 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 > -- See for yourself how easy it is to manage files today. Join the revolution! Razuna SaaS On-Demand - Hosted Digital Asset Management Solution http://www.razuna.com/ Razuna - Open Source Digital Asset Management http://www.razuna.org/ Follow us on Twitter http://twitter.com/razunahq -- 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
