preparedstatment error

Question

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.

Answer 1

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);

Answer 2

I re-wrote your query as:

SELECT TO_CHAR(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 
  FROM newappl a
  JOIN newappl_hq b ON b.appl_no = a.appl_no
 WHERE a.appl_no = ?
   AND rownum = 1 
   AND credit_status = 'CRPEND'
UNION ALL
SELECT TO_CHAR(SYSDATE,'DD MONTH,YYYY'),
       a.appl_no,
       a.assigned_to,
       c.trading_name, 
       ' ', 
       d.bank_acct_no, 
       d.bank_no, 
       d.bank_branch_no,
       a.service_id
  FROM newappl a
  JOIN newappl_ret c ON c.appl_no = a.appl_no
  JOIN newappl_ret_bank d ON d.ret_id = c.ret_id
 WHERE c.appl_no = ?
   AND rownum = 1 
   AND credit_status = 'CRPEND'

From what I can see, the ORA-00918 is about the reference to the credit_status column. Of the tables involved, is there a credit_status column in more than one of them? Because it's the only un-aliased column in either query.

Couple other things to mention:

• don't need to TRUNC a date if you're going to TO_CHAR it for just the day/month/year info.
• don't need to alias columns in the latter part of UNION'd statements, UNIONs only need the same number of columns in the SELECT clause and that their data types match
• don't subquery for what you don't need to
• always test the query in PLSQL Developer/etc before dumping it into a Prepared Statement. This looks like it could be a stored procedure with a single parameter (assuming the appl_no is identical for both sides)