You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Sophia <jd...@telenet.be> on 2008/06/19 14:54:48 UTC

Problem with my query.

Hallo everyone, 

I have a problem with my query, can someone help me further
This is the query that I want to use in ibatis.

select * from ACCOUNT where NUMBER in
(select NUMBER from ACCOUNT where substr(ACCOUNT,5,4) between '2002' and '2006’)


This is my source:


try {
dao.ACCOUNTDAO accountList2Dao = (dao.ACCOUNTDAO)DaoManagerRequester.getDaoManager().getDao(dao.ACCOUNTDAO.class);
model.ACCOUNTExample account2Example = new model.accountExample();    
     
List resultList6 = accountList2Dao.selectByAccountList2(account2Example);
account2Example.createCriteria().andNumberDateBetween(startdate, enddate);
    	  
List resultList6 = accountList2Dao.selectByAccountList2(account2Example);    	
...

} catch ...


This works, and it gives me this:

select * from ACCOUNT where substr(ACCOUNT,5,4) between '2002' and '2006'


Any suggestions?
 

Regards,
Sophia

Re: Problem with my query.

Posted by Da...@ltx.com.
I'm not entirely sure what you're trying to do, as that query at the end 
of your message looks like a good solution to me.  It sounds like you are 
trying to do something more complicated.  Depending on your DBMS, you can 
try something like this:

select ACCOUNT.* from ACCOUNT,
        (select NUMBER from ACCOUNT where substr(ACCOUNT, 5, 4) between 
'2002' and '2006') NUM
where ACCOUNT.NUMBER = NUM.NUMBER

I'm sure any number of people can give you any number of ways to optimize 
this.  Is the error that you get coming from your DBMS or ibatis?

-David



From:
"Sophia" <jd...@telenet.be>
To:
user-java@ibatis.apache.org
Date:
06/19/2008 08:55 AM
Subject:
Problem with my query.



Hallo everyone, 

I have a problem with my query, can someone help me further
This is the query that I want to use in ibatis.

select * from ACCOUNT where NUMBER in
(select NUMBER from ACCOUNT where substr(ACCOUNT,5,4) between '2002' and 
'2006?)


This is my source:


try {
dao.ACCOUNTDAO accountList2Dao = 
(dao.ACCOUNTDAO)DaoManagerRequester.getDaoManager().getDao(dao.ACCOUNTDAO.class);
model.ACCOUNTExample account2Example = new model.accountExample(); 
 
List resultList6 = accountList2Dao.selectByAccountList2(account2Example);
account2Example.createCriteria().andNumberDateBetween(startdate, enddate);
 
List resultList6 = accountList2Dao.selectByAccountList2(account2Example);  
 
...

} catch ...


This works, and it gives me this:

select * from ACCOUNT where substr(ACCOUNT,5,4) between '2002' and '2006'


Any suggestions?
 

Regards,
Sophia