CODEWITHSUNDEEP
oracle-databasein-clauseora-01795
Sadi
Sadi

Reputation: 1

Oracle maximum number of expression issue

I have this query

@Query("SELECT b from BillInfoDetails b where b.masterAcctCode in :masterAccountList and b.msisdn in :msisdnList") List<BillInfoDetails>findAllByMsisdnAndMasterAcctList(@Param("masterAccountList")List<String> masterAccountList, @Param("msisdnList") List<String> msisdnList);

and then find this error ORA-01795: maximum number of expressions in a list is 1000. This error has some manual solution that I have split the list manually 1 to 999 and then 1000 to 1999 and so on. But this will not the good one for me cause in this msisdnList there could be 1500 or 18000 or some other more values. Moreover I want a dynamic solution actually where any dynamic value whatever it is, it will work properly

Upvotes: 0

Views: 59

Answers (1)

Littlefoot
Littlefoot

Reputation: 142710

One option is to store all those values into a table; then you'd be able to use it as a join (or a subquery). For example:

select b.*
from billinfodetails b join new_table n on n.masteracctcode = b.masteracctcode

or

select b.*
from billinfodetails b
where b.masteracctcode in (select n.masteracctcode
                           from new_table n)

or

select b.*
from billinfodetails b
where exists (select null
              from new_table n
              where n.masteracctcode = b.masteracctcode)

Upvotes: 0

Related Questions