3 Replies Latest reply: Jun 29, 2011 1:55 PM by Ralf Becher RSS

    Looping with select and store

      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

       

      ;

        • Looping with select and store
          Ralf Becher

          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

            • Looping with select and store

              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

               

              ;