Looping in a field in Script

[azmeerrehan]

I have created a  QVW which checks for today's date in an Audit table from Database.  If Today's Date exits in the table then it suppose to exit which will trigger the next load to start, but if the date is not today it loops every 5 secs to check for today's date. I would like to add the below 2 things:

1) If the date is not today then check every 5 secs do it 5 times and then exit with a load failure.

LET vDWDate = null();

LET vToday = num(today());

// Start loop script that will continue until condition is met.

Do until vDWDate=vToday

Trigger:

Select  DISTINCT DATE_TIME as DWDate

FROM AUDIT_DATA

where DATE_TIME = (Select max(DATE_TIME) FROMFROM AUDIT_DATA) ;

LET vDWDate = peek('DWDate');

// where condition is met, exit and complete.

if vDWDate=vToday then

  Exit Do

// where condition is not met, drop that table ready to restart the load process.

else

DROP TABLE Trigger;

// The sleep means that we can control the frequency of calls to the source database (or file).

Sleep 5000;  // 10000 milliseconds = 10 seconds.

end if

// where condition is not met then restart loop.

loop

exit script;

[tamilarasu]

HI Rehan,

LET vDWDate = null();
LET vToday = num(today());
Set vCounter = 1;

// Start loop script that will continue until condition is met.
Do until vDWDate=vToday

Trigger:
Select  DISTINCT DATE_TIME as DWDate
FROM AUDIT_DATA
where DATE_TIME = (Select max(DATE_TIME) FROMFROM AUDIT_DATA);

LET vDWDate = peek('DWDate');

// where condition is met, exit and complete.
if vDWDate=vToday then

    Exit Do

// where condition is not met, drop that table ready to restart the load process.

elseif vCounter =5 then
   

    TRACE 'Date does not exist!!';   //Manual error message that will be captured in log
    SET TriggerError; //This line of code triggers an error 
    Exit Do  //or exit script;

Else
  

    DROP TABLE Trigger;
   //The sleep means that we can control the frequency of calls to the source database (or file).
    Sleep 5000;  // 10000 milliseconds = 10 seconds.
    Let vCounter = vCounter +1;   //Increasing counter on each load

ENDIF

// where condition is not met then restart loop.
loop

exit script;