Referencing a list in a where clause and getting ORA-01795 error

[rjs2]

I have a query that produces a list of outstanding checks. I need to make another query referencing a different table to get information on the payee of the check.  I want to limit that second query only to the accounts in the first querry.  I had made a seperate table referencing the first query and removing all columns other than the account number and converted it to a list using this in my WHERE clause: AND ""Table"".""Field"" IN ('" & Text.Combine(List, "','") & "').  Which returns the ORA-01795 error.  My list is greater than 1,000.

So I decided to try to pass Tuples instead making my list into {'AcctCode1','D1'} in hopes that I could pass the tuples in the list to be this {'AcctCode1','D1'},{'AcctCode2','D1'},{'AcctCode2','D1'},etc...  Then changing my WHERE clause to this: AND ""Table"".""Field"" IN (" & Text.Combine(List, ",") & ") since I have the quotes in the list now due to the formating.

I believe I am missing something in my formating or code for I keep getting this: "DataSource.Error: Oracle: ORA-00936: missing expression"  Keep in mind I my AcctCode is Alphanumeric, so I need the quotes.

Again my list is {'AcctCode1','D1'} and my statement in the WHERE clause is:   AND ""Table"".""Field"" IN (" & Text.Combine(List, ",") & ")

Also I am loading the data, this isnt a direct query.

Or is there a way to split a list up without physically splitting it for the list will ever be expanding and contracting depending on the number of stale checks.

DataSource.Error: Oracle: ORA-01795: maximum number of expressions in a list is 1000

[v-luwang-msft · Accepted solution]

Hi @rjs2 ，

You have not provided your specific query, so the code used after your where is not very clear, you can refer to the following sample for adjustment.

Before:

select field1, field2, field3 
from table1 
where name in 
(
'value1',
'value2',
...
'value10000+'
);

After:(Just use multiple in-clauses to get around this)

select field1, field2, field3 from table1 
where  name in ('value1', 'value2', ..., 'value999') 
    or name in ('value1000', ..., 'value1999') 
    or ...;

Similar error refer:

https://stackoverflow.com/questions/17842453/is-there-a-workaround-for-ora-01795-maximum-number-of-e... 

https://stackoverflow.com/questions/51771089/ora-01795-maximum-number-of-expressions-in-a-list-is-10... 

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[DataInsights]

@rjs2,

Are both tables in Oracle? If so, you could use custom SQL. If both tables are not in Oracle, you could use an inner join in Power Query to filter the relevant rows, and then delete the unneeded columns.