Hi Bruce,
I tried it but i'm getting an exception. Here is my SQL and the error
message I get.
SELECT p FROM com.PCPgo.prm.web.location.LocationICDJdo p where
p.icdCodeJdo.icdId NOT IN LIST (SELECT l.icdId FROM
com.PCPgo.prm.web.location.LocationICDJdo WHERE l.loLocationId = $1)
org.exolab.castor.jdo.oql.OQLSyntaxException: An incorrect token type was
found near NOT (found KEYWORD_NOT, but expected END_OF_QUERY)
at org.exolab.castor.jdo.oql.Parser.match(Parser.java:208)
at org.exolab.castor.jdo.oql.Parser.getParseTree(Parser.java:170)
at
org.exolab.castor.jdo.engine.OQLQueryImpl.create(OQLQueryImpl.java:269)
at
org.exolab.castor.jdo.engine.DatabaseImpl.getOQLQuery(DatabaseImpl.java:467)
at
com.PCPgo.prm.web.encounter.AssessmentDB.search(AssessmentDB.java:113)
at
com.PCPgo.prm.web.encounter.AssessmentAction.execute(AssessmentAction.java:2
31)
at
org.apache.struts.action.RequestProcessor.processActionPerform(RequestProces
sor.java:465)
at
org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
at
org.apache.struts.action.ActionServlet.process(ActionServlet.java:1422)
at
org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:523)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Application
FilterChain.java:247)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterCh
ain.java:193)
at
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.ja
va:260)
at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
eNext(StandardPipeline.java:643)
at
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at
org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.ja
va:191)
at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
eNext(StandardPipeline.java:643)
at
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at
org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at
org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2396)
at
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180
)
at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
eNext(StandardPipeline.java:643)
at
org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.
java:170)
at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
eNext(StandardPipeline.java:641)
at
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:172
)
at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
eNext(StandardPipeline.java:641)
at
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at
org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java
:174)
at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
eNext(StandardPipeline.java:643)
at
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at
org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at
org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:223)
at
org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:405)
at
org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConne
ction(Http11Protocol.java:380)
at
org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:508)
at
org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.jav
a:533)
at java.lang.Thread.run(Thread.java:536)
Am I doing anything wrong in the query after NOT or the keyword NOT IN is
not supported bye castor?
Thanks,
Aditya
-----Original Message-----
From: Bruce Snyder [mailto:[EMAIL PROTECTED]
Sent: Friday, May 30, 2003 3:59 PM
To: [EMAIL PROTECTED]
Subject: Re: [castor-dev] How can I use NOT IN -- keyword
This one time, at band camp, Aditya Akella said:
AA>How can I use NOT IN -- keyword with the SQL statement in Castor. I was
able
AA>to make it work with CALL SQL as it is a SQL statement.
AA>
AA>Here is a example CALL SQL Statement.
AA>
AA>CALL SQL SELECT * from CPT_CODE where code like '%A' and CPT_ID NOT IN
AA>(SELECT CPT_ID FROM PROVIDER_CPT_PREFS WHERE PL_LOCATION_ID=1) AS
AA>CptCodeJdo;
AA>
AA>I want to do something like this
AA>
AA>SELECT p FROM CptCodeJdo p where p.cptCode LIKE $1 and p.cptId NOT IN
AA>(...........);
AA>
AA>I'm not able to use the NOT IN like this. Any code snippet would be very
AA>helpful.
Aditya,
See the JDO FAQ:
http://www.castor.org/jdo-faq.html#OQL
See the question, 'Does Castor support the SQL 'IN' expression?'
So I'm guessing that the syntax for NOT IN would be:
SELECT p FROM Product p WHERE p.id NOT IN LIST ( 123, 456, 789 )
I haven't tested this yet, so please let me know the outcome. Then I'll
update the FAQ.
Bruce
--
perl -e 'print
unpack("u30","<0G)[EMAIL PROTECTED]&5R\"F9E<G)E=\$\!F<FEI+F-O;0\`\`");'
-----------------------------------------------------------
If you wish to unsubscribe from this mailing, send mail to
[EMAIL PROTECTED] with a subject of:
unsubscribe castor-dev
-----------------------------------------------------------
If you wish to unsubscribe from this mailing, send mail to
[EMAIL PROTECTED] with a subject of:
unsubscribe castor-dev
