Database Read Query Step – wrong number of rows

Answered

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!

database read query step database

NewUser asked 12 months ago

You must login to post comments

Answers (1)

Best Answer

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.

vanja.utkovic answered 11 months ago

You must login to post comments

Your Answer

Please login first to submit.