2 Replies Latest reply: Jul 16, 2012 3:23 PM by Marc Livingston RSS

    Issue with Incremental reload. Losing data that should not be lost

      I am having an issue with incremental reload losing data. Below is a section of my script that is causing issues. I have narrowed this issue down to being caused to the WHERE NOT EXISTS part. i.e. I commented that out and the data I want shows up. I checked the first section of the SQL select and the data I want is not there, and the primarykeyJournaldtl is not there. I have no Idea what could be causing this as I have double checked to make sure it is not being loaded in the first section, and I do know that it is contained within the QVD.

       

      This is beginning to give me a major headache so any help would be appreciated.

       

       

       

       

      Let ThisExecTime= num(today());
      Let ThisExecTimeyear= year(today());
      Let ThisExecTimemonth= num(month(today()));
      Let ThisExecTimeday= day(today());
      
      Let LastExecTime =  ThisExecTime;                                                                
      Let LastExecTimeyear=ThisExecTimeyear;
      Let LastExecTimemonth=ThisExecTimemonth;
      Let LastExecTimeday=ThisExecTimeday;
      
      
      Journaldtl1:
      SQL SELECT 
      company  + bookid + cast(fiscalyear as varchar)+ cast(journalcode as varchar) + cast(journalnum as varchar) 
      + cast(journalline as varchar)  as primarykeyJournaldtl,
          *
          FROM gljrndtl where
       ((CAST((  STR( ( YEAR(posteddate) ) ) + '/' +  STR( MONTH(posteddate) ) + '/' +  STR( DAY(posteddate) )  ) AS DATETIME) ) >=
       CAST((  STR( ( $(LastExecTimeyear) ) ) + '/' +  STR( ($(LastExecTimemonth)) ) + '/' +  STR(($(LastExecTimeday)) )  ) AS DATETIME)  
       AND 
       (CAST((  STR( ( YEAR(posteddate) ) ) + '/' +  STR( MONTH(posteddate) ) + '/' +  STR( DAY(posteddate) )  ) AS DATETIME) ) <=
        CAST((  STR( ( $(ThisExecTimeyear) ) ) + '/' +  STR( ($(ThisExecTimemonth)) ) + '/' +  STR(($(ThisExecTimeday)) )  ) AS DATETIME) )   
      ;
      
      Concatenate 
      LOAD 
          *
          FROM [....qvd(qvd)] (qvd)
      WHERE NOT EXISTS(primarykeyJournaldtl)
      ;
      
      Inner Join SQL SELECT company,bookid,fiscalyear,journalcode,journalnum,journalline FROM gljrndtl;