Hi Team,
   Given the following script:
{sql}
SCRIPT (IN EndDate Date, OUT RecCount Integer)
BEGIN

  EXECUTE IMMEDIATE 'insert into ajobtickets
  select * from jobtickets
  where (jobstatus = ''CL'' or jobstatus = ''CA'') and Created < ?' using EndDate;

  execute immediate 'delete from JobTickets
  where (jobstatus = ''CL'' or jobstatus = ''CA'') and Created < ?' using EndDate;

  execute immediate 'INSERT INTO ALineItems
  SELECT * from LineItems
  WHERE LineItems.JobNo IN (SELECT JobNo FROM AJobTickets)';

  execute immediate 'DELETE FROM LineItems
  WHERE JobNo IN (SELECT JobNo from ALineItems)';

  execute immediate 'INSERT INTO ATechData
  SELECT * from TechData
  WHERE TechData.JobNo IN (SELECT JobNo FROM AJobTickets)';

  execute immediate 'DELETE FROM TechData
  WHERE JobNo IN (SELECT JobNo from ALineItems)';

END
{sql}

   How do I populate RecCount with the number of records processed by the First or Second EXECUTE please?

Regards & TIA,
Ian

Ian


I think you're looking for the ROWSAFFECTED function but you may have to rewrite a little to use EXECUTE rather than EXECUTE IMMEDIATE. There's a good example in the OLH in EDBManager.

Roy Lambert

Hi Roy,
   Thanks for the pointer.  Ended up with this..
{sql}
SCRIPT (IN EndDate Date, OUT RecCount Integer)
BEGIN
  DECLARE sEndDate VARCHAR(10) DEFAULT cast(EndDate as VARCHAR);
  DECLARE InsertStmt Statement;

  PREPARE InsertStmt from 'insert into ajobtickets
  select * from jobtickets
  where (jobstatus = ''CL'' or jobstatus = ''CA'') and Created < DATE '+QUOTEDSTR(sEndDate);

  EXECUTE InsertStmt;

  SET RecCount = RowsAffected(InsertStmt);
....
....
{sql}

Regards & Tks again,
Ian