0

I have the input as customer number (CSR_ID BigDecimal(11,0)). The below code fails as Oracle query expression has the limitation of 1000:

//Start of the code
 String cust_ids = null;
 int cnt_cust    = 0;
 java.util.StringJoiner joiner = new java.util.StringJoiner(","," ( "," ) ");

 //This is a loop which gets CSR_ID as input:
   joiner.add(row5.CSR_ID.toString());
    cnt_cust++;

//End of the code
   System.out.println(joiner); // (1,2,3,...,100045)
   cust_ids = joiner.toString();

Query looks like:

"select col1, col2 from customers where cust_id in " +  
"(1,2,3,...,100045)";

I wanted to split this like below:

"select col1, col2 from customers where" +  
" cust_id in (1,2,3...,1000)" +
" or cust_id in (1001,.....2000)" +
.....   ;

How do I build this query expression in bulk of 1000 customer-id. Note: customer-id is not be sequential, an random values.

Aavik
  • 967
  • 19
  • 48
  • Don't use string concatenation to build the query use a parameterised query and if you need to pass an array of values then bind a [Java array parameter to an Oracle collection](https://stackoverflow.com/a/34699771/1509264) or use a temporary table. – MT0 Oct 03 '19 at 10:30

2 Answers2

0

I think you can take advantage of multi-column comparision in IN as following:

select col1, col2 from customers where cust_id in   
(1,2,3,...,100045); -- This will fail

select col1, col2 from customers where (cust_id,1) in   
((1,1),(2,1),(3,1),...,(100045,1)); -- This will work

Cheers!!

Popeye
  • 35,427
  • 4
  • 10
  • 31
0

A solution without refinement is shown as follows and I think there are more elegant ways by using Stream in Java 8.

The concept is to transform the cust_ids into List, then you can divide the list with desired block size.

    int blockSize = 1000;
    List<String> cust_idsList = Arrays.asList(cust_ids.substring(1, cust_ids.length()-1).split(","));
    System.out.println(cust_idsList.size());

    StringBuilder sb = new StringBuilder();
    sb.append("select col1, col2 from customers where cust_ids in (");
    for (int i = 0; i < cust_idsList.size(); i++) {
        sb.append(cust_idsList.get(i)).append(",");
        if (i !=0 && i%blockSize == blockSize-1) {
            sb.setLength(sb.length()-1);

            if (i != cust_idsList.size()-1) {
                sb.append(")\n or cust_ids in (");
            }
        }
    }
    sb.setLength(sb.length()-1);
    sb.append(")");

    System.out.println(sb.toString());
LHCHIN
  • 3,679
  • 2
  • 16
  • 34