Customize Consent Preferences

We use cookies to help you navigate efficiently and perform certain functions. You will find detailed information about all cookies under each consent category below.

The cookies that are categorized as "Necessary" are stored on your browser as they are essential for enabling the basic functionalities of the site. ... 

Always Active

Necessary cookies are required to enable the basic features of this site, such as providing secure log-in or adjusting your consent preferences. These cookies do not store any personally identifiable data.

Functional cookies help perform certain functionalities like sharing the content of the website on social media platforms, collecting feedback, and other third-party features.

Analytical cookies are used to understand how visitors interact with the website. These cookies help provide information on metrics such as the number of visitors, bounce rate, traffic source, etc.

Performance cookies are used to understand and analyze the key performance indexes of the website which helps in delivering a better user experience for the visitors.

Advertisement cookies are used to provide visitors with customized advertisements based on the pages you visited previously and to analyze the effectiveness of the ad campaigns.

Database Read Query Step – wrong number of rows

Answered
0
0

Hello everyone,

I’m encountering an issue with the Database Read Query step. Each time I run it, the row count varies. There are 513,501 rows in the database, but when the robot pulls them, I get different counts (e.g., 330,000, 356,100, 358,000).

When I run SQL reports, I get a count of 351,700, and when I start the process, I get 447,801.

Thanks in advance!

  • You must to post comments
Best Answer
0
0

Hello,

 

Here how I managed this issue:

Get the Total Row Count: First, run a SQL query to get the total number of rows in the table. This helps to determine how many iterations will be needed to fetch the data in chunks.

e.g. SELECT COUNT(*) FROM test_uplate;

 

Calculate the Number of Iterations: Divide the total number of rows by the chunk size (e.g., 100,000 rows per chunk) and round up to the nearest integer. This gives the number of iterations required.

string totalCountString = (string)VR[“totalCountString”]; // result of COUNT(*)
double totalCount = double.Parse(totalCountString);
int span = (int)VR[“span”]; // 100,000

int loopNum = (int)Math.Ceiling(totalCount / span);
MessageBox.Show(“Number of iterations: ” + loopNum.ToString());

VR[“totalCount”] = totalCount;
VR[“loopNum”] = loopNum;

Create a DataTable: Initialize a DataTable with the same columns as the SQL SELECT statement. This table will be used to store the fetched rows from each iteration.

DataTable finalDT = new DataTable();
finalDT.Clear();

finalDT.Columns.Add(“RACUN”);
finalDT.Columns.Add(“VALUTA”);
finalDT.Columns.Add(“DATUM”);
finalDT.Columns.Add(“IZNOS”);
finalDT.Columns.Add(“OPIS”);
finalDT.Columns.Add(“TIP_PROIZVODA”);
finalDT.Columns.Add(“TECAJ”);
finalDT.Columns.Add(“IZNOS_HRK”);

VR[“finalDT”] = finalDT;

Fetch Data in Chunks: Use a loop to fetch the data in chunks. The SQL query uses OFFSET and FETCH NEXT to fetch rows in batches. Each chunk is added to the final DataTable.

SELECT *
FROM LINC.test_uplate
WHERE RACUN = ‘xxxx394’
AND valuta = ‘EUR’
and TRUNC(datum) >= TO_DATE(‘01.01.23′,’dd.mm.yy’)
and TRUNC(datum) <= TO_DATE(‘30.06.23′,’dd.mm.yy’)
OFFSET *rowNum* ROWS FETCH NEXT *span* ROWS ONLY

Save Data to Excel: After all iterations are complete and the data is consolidated into the final DataTable, save it to an Excel file.

  • You must to post comments
Showing 1 result
Your Answer

Please first to submit.