Looping with select and store

[#1]

Hi ,

   I'm writing Qv scripting to fetch data from a huge table.Since the tabel is huge and I have to fetch data for around 3 years , I'm using looping technic to fetch data for each month and store it in qvd like incrmental load. below is the sample script I'm using.

I have verified query it works fine in DB. But, some how the QV  script is failing to execute. Please advice.

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

ODBC CONNECT TO DB;

//concatenate

Let StartDate = DAYNAME('1/1/2008');

Let limitDate = addmonths ($(StartDate),1);

Do WHILE ( $(limitDate) < Today([0]))

temp:

SELECT   

                COL1,

                  COL2...

                FROM    

                TABLE A

            WHERE  

              and  A.datecol >=   $(StartDate)

              and  A.datecol < $(limitOASDate)  ;

Concatenate

  LOAD

    *

  From

  D:\temp\temp.qvd

  (qvd);

STORE temp intoD:\temp\temp.qvd

;

drop table temp;

let StartDate= $(limitOASDate);

let limitDate = addmonths( $(limitDate) , 1);

Loop

;

[rbecher]

I don't know why your are doing this, I see no benefit, but there are some typos:

Today([0])    >>  today(0)

STORE temp intoD:\...  >> STORE temp into D:\..

Btw, where the limitOASDate is defined?

- Ralf

[#3]

Please check the updated code:  I'm doing as the source table is huge and query is getting stuck to fetch 3 years worth data in single shot. hence, I'm splitting it into monhs and trying to do a incremental load.

Let me know if this logic supported by QV.

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

ODBC CONNECT TO DB;

//concatenate

Let StartDate = DAYNAME('1/1/2008');

Let limitDate = addmonths ($(StartDate),1);

Do WHILE ( $(limitDate) < Today([0]))

temp:

SELECT   

                COL1,

                  COL2...

                FROM    

                TABLE A

            WHERE  

              and  A.datecol >=   $(StartDate)

              and  A.datecol < $(limitDate)  ;

Concatenate

  LOAD

    *

  From  D:\temp\temp.qvd

  (qvd);

STORE temp into D:\temp\temp.qvd ;

drop table temp;

let StartDate= $(limitDate);

let limitDate = addmonths( $(limitDate) , 1);

Loop

;

[rbecher]

Why not just w/o the Store?

Do While

temp:

ADD SQL SELECT.....

Where...;

Loop;