Looping through a hugh qvd (170 plus million rows)to generate qvds by year, ends up generating empty qvds

[Swarup_MOA]

Hello,

I ran into a unique situation today. Looks like a bug to me.

I have a huge qvd ( almost a billion rows) containing 4 years of data.  I tried to loop through the qvd to segment it into multiple qvds by year, it is generating empty qvds. Th code works when I tried to do a limited load of upto 1 million rows, fails for larger datasets

pasting the psedu code:

//extracting the distinct years

TempYear:

Load *;

SELECT     distinct left(MY_Date_Field, 4) as YR

FROM

MY_table

 ;   

for each i in FieldValueList('YR')

  
TempSales:

Load *

 FROM [$(vSourceQvd)/BIG_DataSet_unfiltered.qvd]
(qvd)
WHERE   left(MY_Date_Field, 4)=$(i)  
  ;

let vFileName='MYQVD_' & $(i);
// sleep 90000 ; //wait for 1.5 minutes
store TempSales into [$(vTransformQvd)/$(vFileName).qvd](qvd);
  
drop table TempSales ;
next ;

[Swarup_MOA]

It works for smaller dataset, but fails for larger dataset by generating empty qvds

[rwunderlich]

Take a look at the execution script log. What does the generated Where statement look like. Are there any record counts?

-Rob

[Swarup_MOA]

Hi Rob,

I checked the execution log. It shows 0 rows when I try to do a full reload. But the same script works  when I try to limit the row count to 1 million by using :

First 1000000

Load *

from Myqvd;

The full reload should generate around 160 million rows, it seems to fail for big data sets.