In a SQLExecuter, I'm using a SELECT statement where PKey IN (@Value(ID)
I'm getting the oracle error:ORA-01795: maximum number of expressions in a list is 1000,as the number of of concatenated ID's passing the query is exceeding the 1000 limit.
so I used an Aggregator and a Smapler trying to split the stream of data entering the SQLExecuter (every 1000 features), but that didn't work as I wish.. (pass a pack of every 1000 features to SQLEXECUTER)
Any idea?
Yes, and its not my solution ( thanks to @erik_jan ):
If you have >1000 features (ID's) you will need to do the following:
Add a Counter starting with 0
Add an ExpressionEvaluator with floor(@Value(_count)/1000).
Add an Aggregator with group by on _result and a list _list, attribute to concatenate > values where in clause
Add a StringConcatenator to create the IN part of the WHERE clause using the
concatenated attribute attribute.
This way you get IN clauses with a max of 1000 values.
Hope this helps, it did help me countless times.
Yes, and its not my solution ( thanks to @erik_jan ):
If you have >1000 features (ID's) you will need to do the following:
Add a Counter starting with 0
Add an ExpressionEvaluator with floor(@Value(_count)/1000).
Add an Aggregator with group by on _result and a list _list, attribute to concatenate > values where in clause
Add a StringConcatenator to create the IN part of the WHERE clause using the
concatenated attribute attribute.
This way you get IN clauses with a max of 1000 values.
Hope this helps, it did help me countless times.
Thanks itay..That works like a charm!
My two cents to add here is it to utilize the Aggregator to concatenate the IDs (one less transformer StringConcatenator). as shown in the below snapshot. so a counter, ExpressionEvaluator, and an Aggregator can do the job.
FME rocks!
Yes, and its not my solution ( thanks to @erik_jan ):
If you have >1000 features (ID's) you will need to do the following:
Add a Counter starting with 0
Add an ExpressionEvaluator with floor(@Value(_count)/1000).
Add an Aggregator with group by on _result and a list _list, attribute to concatenate > values where in clause
Add a StringConcatenator to create the IN part of the WHERE clause using the
concatenated attribute attribute.
This way you get IN clauses with a max of 1000 values.
Hope this helps, it did help me countless times.
Thanks itay..That works like a charm!
My two cents to add here is it to utilize the Aggregator to concatenate the IDs (one less transformer StringConcatenator). as shown in the below snapshot. so a counter, ExpressionEvaluator, and an Aggregator can do the job.
FME rocks!
Do you think this option has to be set to Yes by default in Aggregator?
Thanks a lot !
Do you think this option has to be set to Yes by default in Aggregator?
Thanks a lot !
In your case, the input features are definitely ordered by group, but in general, the order of features is unknown. I therefore think it's better to set No to the parameter by default so that the features will be aggregated as expected regardless of their order.
However, if you know that features are ordered by group almost always for your job, you can set Yes to the parameter as your own default setting through the uDefaults] button in the parameters dialog. Check .Defaults] > Save as My Defaults after setting Yes to the parameter.