Hi,
I am using Query database smart service in the Process model to get the data from the external data source which is in DataMart. Due to some access restriction, we were not able access external database. So, we have added the source in the admin console, and we are using it in smart service. I am getting 1000 rows of data, and I am storing it in a Process variable which is of CDT type. I want to fetch all the rows like I have more than 4000 rows of data. But I am able to fetch only 1000 rows. Is there any way to fetch all the rows. This is my current configuration. We need to display the data in an interface grid which will be viewed in Site
Discussion posts and replies are publicly visible
Query Database smart service has a 1000-row hard limit. Use a loop in your process model with LIMIT 1000 OFFSET ac!offsetValue, starting at 0 and incrementing by 1000 each iteration. Stop the loop when returned rows < 1000 and append each batch to your CDT list PV. Pass ac!offsetValue as an input node in the Data tab of the smart service, starting at 0 and dynamically passing the updated offset after each iteration.
SELECT SOURCE_ID, ADD_COMMENT, MAX_INVOICE_DATE, AMOUNT FROM AHN_EXCEPTIONS_BY_SALES_VW ORDER BY SOURCE_ID OFFSET ac!offsetValue ROWS FETCH NEXT 1000 ROWS ONLY