5 Replies Latest reply: Nov 8, 2017 1:45 AM by Shraddha Gajare RSS

    Incremental Load from SQL table Simple

    vikas mahajan

      Hi all ,

       

      I have this SQL statement  I Wanted to apply incremental load on the same  TRX_DATE is a transaction date for the fact table

       

      how to do this please guide ?

       

      Select

              [MERCHANT_ID], b.ACCOUNT_ID

             ,[TERMINAL_ID]

             ,[BATCH_ID]

             ,[STAFF_ID]

             ,[AMOUNT]

             ,[RATE]

             ,[VOLUME]

             ,[VEHICLE_NO]

             ,[TRX_DATE]

             ,[SETTLEMENT_DATE]

             ,[NOZZLE_ID]

             ,[VEHICLE_SEGMENT]

             ,[TOTALIZER_READING]

             ,[TRX_START_DATE]

             ,[PRESET_TYPE]

             ,[PRESET_VALUE]

          ,b.AUTOMATION_TYPE

          ,b.ME_SAPCC as CCNO

          ,b.ME_NAME

          ,b.ME_STP

          ,b.ME_CITY

        FROM [RA].[dbo].[RO_TRANSACTION_DETAILS] a

        Inner Join [RA].[dbo].[RA_ACCOUNTS] b ON a.MERCHANT_ID = b.ACCOUNT_ID ;--b.ACCOUNT_TYPE='SA';

        --where TRX_DATE >= '01-OCT-2017' and  TRX_DATE <= '2-Oct-2017';

       

      Vikas

        • Re: Incremental Load from SQL table Simple
          Shraddha Gajare

          Normal Incremental Load script and format the Date according to that of TRX_DATE.

          where TRX_DATE >= '01-OCT-2017' and  TRX_DATE <= '2-Oct-2017';

          This line will have variables instead of Oct dates.

            • Re: Incremental Load from SQL table Simple
              vikas mahajan

              Hi Shraddha ,

              In this table we have millions of records , seeking solutions for incremental load which will capture only

              latest records while pulling from SQL.

               

              Can you please share script or some example if you have ?

              Vikas

                • Re: Incremental Load from SQL table Simple
                  Shraddha Gajare

                  You will have to fetch one tym records for some date.(Say you have fetch records till 2nd Oct 2017) and store it in qvd as Trx.Qvd

                   

                   

                  Try below:

                   

                  MAXData:

                   

                  Load

                   

                  Max(TRX_DATE) as Last_Trx_Date

                   

                  From .....Trx.qvd;

                   

                  let vMinInc= num(peek('Last_Trx_Date'));

                  let vMaxInc=num(today());/* Max Date for inc load Load*/

                   

                  Drop table MAXData;


                  /* Creating all dates from Min to Max */

                   


                  Temp:
                  Load

                  date($(vMinInc) + RowNo() -1) as Date,
                  AutoGenerate 1


                  While date($(vMinInc) + RowNo() -1) < date($(vMaxInc));

                   

                  /* Loading Temp table to calculate no of rows*/

                  TEMP:
                  Load Distinct
                  Date
                  Resident Temp;

                  Drop table Temp;

                   

                  Let vCount = NoOfRows('TEMP'); /*calculate no of rows i.e no of Dates*/

                   

                  For i=0 to $(vCount)-1; /* loop to start with concatenating all Daily data*/

                   


                  let vDate = date(peek('Date',$(i),'TEMP'),'DD-MMM-YYYY');

                   

                  TableName:

                  Select

                          [MERCHANT_ID], b.ACCOUNT_ID

                         ,[TERMINAL_ID]

                         ,[BATCH_ID]

                         ,[STAFF_ID]

                         ,[AMOUNT]

                         ,[RATE]

                         ,[VOLUME]

                         ,[VEHICLE_NO]

                         ,[TRX_DATE]

                         ,[SETTLEMENT_DATE]

                         ,[NOZZLE_ID]

                         ,[VEHICLE_SEGMENT]

                         ,[TOTALIZER_READING]

                         ,[TRX_START_DATE]

                         ,[PRESET_TYPE]

                         ,[PRESET_VALUE]

                      ,b.AUTOMATION_TYPE

                      ,b.ME_SAPCC as CCNO

                      ,b.ME_NAME

                      ,b.ME_STP

                      ,b.ME_CITY

                    FROM [RA].[dbo].[RO_TRANSACTION_DETAILS] a

                    Inner Join [RA].[dbo].[RA_ACCOUNTS] b ON a.MERCHANT_ID = b.ACCOUNT_ID ;--b.ACCOUNT_TYPE='SA';

                    --where TRX_DATE = '$(vDate)' ;

                   

                  Next

                   

                  Concatenate

                   

                  Load *

                   

                  From ....Trx.qvd;

                   

                  Store Tablename into ....Trx.qvd;