I have two select statements and make 'union all' for these two statements. Then, I use the PreparedStatement and when I setString to this preparedStatement, it shows "java.sql.SQLException: Missing IN or OUT parameter at index:: 2".
After I toured around google, some people say that for each "?" in sql statment, I should write setString. For my situation, I have two select statments so I have two "?" but I "union all", so I'm not sure whether it is assumed that one "?" or two "?". But when I tried to write two setString like preparedStatement.setString(1,ApplicationNo); preparedStatement.setString(2,ApplicationNo); , it shows "ORA-00918: column ambiguously defined".
I have no idea how to solve this problem.
my union select statment is
query.append("select TO_CHAR(TRUNC(SYSDATE),'DD MONTH,YYYY'),a.appl_no,a.assigned_to,b.co_name,b.co_name2,a.credit_acct_no,a.credit_bank_no,a.credit_branch_no,a.service_id ");
query.append("from newappl a, newappl_hq b where b.appl_no = a.appl_no and a.appl_no=(select appl_no from newappl where appl_no=?) and rownum=1 and credit_status = 'CRPEND'");
query.append(" union all ");
query.append("select TO_CHAR(TRUNC(SYSDATE),'DD MONTH,YYYY'),a.appl_no,a.assigned_to,c.trading_name co_name, ' ' co_name2, d.bank_acct_no credit_acct_no, d.bank_no credit_bank_no, d.bank_branch_no credit_branch_no,a.service_id ");
query.append("from newappl a,newappl_hq b, newappl_ret c, newappl_ret_bank d where b.appl_no = a.appl_no or a.appl_no = c.appl_no and c.ret_id= d.ret_id and a.appl_no=(select appl_no from newappl_ret where appl_no=?) and rownum=1 and credit_status = 'CRPEND'");*
setString is preparedStatement.setString(1,ApplicationNo);
When I searched for setString example, there are two different parameters if there are two setString like
preparedStatement.setString(1,ApplicationNo);
preparedStatement.setString(2,LoginID);
But I need ApplicationNo for both select statments.
I see no reason why you should be building this query up and gc-ing it away over and over. I'd make it a static final String once and be done with it.
If you need it twice, why can't you do this?
ps.setString(1, applicationNumber);
ps.setString(2, applicationNumber);