Why won't my incremental load work?

[Anonymous]

My initial creation of the QVD file works just fine.  But when the ELSE statement gets executed, the QVD then contains zero records.  If I modify one record in the database after the initial creation of the QVD, I can then see, in the execution of the ELSE statement, that the logic is detecting the 1 modified record, but still the QVD is created with zero records.

IF (isnull(QvdCreateTime('KATIE.qvd'))) then // if qvd file doesnt exist
      KATIE1_SQL:
     SQL
      select * from people_T where first_name='Katie';
     STORE KATIE1_SQL INTO  KATIE.qvd ;
     Let LastExecTime4 = Now( );

ELSE
      Let ThisExecTime4 = Now( );
      KATIE2_SQL:
     SQL 
       select * from people_T where first_name='Katie'
       AND ( LAST_CHANGE_DATE >= to_timestamp('$(LastExecTime4)', 'MM/DD/YYYY HH:MI:SS AM') AND LAST_CHANGE_DATE < to_timestamp('$(ThisExecTime4)',  'MM/DD/YYYY HH:MI:SS AM') ) ; 
       Concatenate LOAD * FROM KATIE.qvd 
      WHERE NOT EXISTS(ID); // unique key
     Inner Join SQL select * from people_T where first_name='Katie';

      If ScriptErrorCount = 0 then
           STORE KATIE2_SQL INTO KATIE.qvd ;
           Let LastExecTime4 = ThisExecTime4;
      EndIf;

ENDIF;

[shraddha_g]

I guess let lastExecTime4 statement after Else is an issue

[Anonymous]

But I see the following query getting executed:

select * from people_T where first_name='Katie'

       AND ( LAST_CHANGE_DATE >= to_timestamp('$(LastExecTime4)', 'MM/DD/YYYY HH:MI:SS AM') AND LAST_CHANGE_DATE < to_timestamp('$(ThisExecTime4)',  'MM/DD/YYYY HH:MI:SS AM') ) ;

So I don't think the Let lastExecTime4 statement is an issue.

[vinieme12]

You are not setting variable value for LastExecTime4 in the <ELSE> part, are you setting this before the if section?

also get rid of the Quotes and try

select * from people_T where first_name='Katie'

       AND ( LAST_CHANGE_DATE >= to_timestamp($(LastExecTime4), 'MM/DD/YYYY HH:MI:SS AM') AND LAST_CHANGE_DATE < to_timestamp($(ThisExecTime4),  'MM/DD/YYYY HH:MI:SS AM') ) ;

[Anonymous · Accepted solution]

The solution is, in the INNER JOIN statement, I had to change "SELECT *" to "SELECT (ID)"

Complete solution to handle new recs, deleted recs, and updated recs:

IF (isnull(QvdCreateTime('KATIE.qvd'))) then // if qvd file doesnt exist

KATIE1_SQL:
SQL
select * from people_T where first_name='Katie';

STORE KATIE1_SQL INTO  KATIE.qvd ;
Let LastExecTime4 = Now( );

ELSE
Let ThisExecTime4 = Now( );

KATIE2_SQL:
SQL 
select * from people_T where first_name='Katie'
AND ( ( LAST_CHANGE_DATE >= to_timestamp('$(LastExecTime4)', 'MM/DD/YYYY HH:MI:SS AM') AND LAST_CHANGE_DATE < to_timestamp('$(ThisExecTime4)', 'MM/DD/YYYY HH:MI:SS AM') )
OR
CREATE_DATE >= to_timestamp('$(LastExecTime4)', 'MM/DD/YYYY HH:MI:SS AM')
);
Concatenate LOAD * FROM KATIE.qvd (qvd)
WHERE NOT (EXISTS(ID));

INNER JOIN (KATIE2_SQL) SQL select (ID) from people_T where first_name='Katie' ;

If ScriptErrorCount = 0 then
STORE KATIE2_SQL INTO KATIE.qvd ;
Let LastExecTime4 = ThisExecTime4;
EndIf;

ENDIF;