Hi Team,
I have already copied selected records from one table to another..
{sql}
insert into ajobs
select * from jobs
where status = 'CA' or (status = 'CO' and paid = true and (date_out > date '2000-01-01' and date_out < date :EndDate))
{sql}
Deleting those copied records from jobs is not a problem.

Now I need to copy all records from table 'Items' to 'AItems' that have the same job # as any of those copied into ajobs.  Then delete those copied into aItems, from Items.

Help!

Regards & TIA,
Ian

Ian


Not to difficult (as long as my understanding and guess  is coirrect)

INSERT INTO AItems SELECT * from Items WHERE Items.Job# IN (SELECT Job~ FROM AJobs)

followed by

DELETE FROM Items WHERE itemid IN (SELECT itemid from AItems)

I'm guessing there's a unique id for Items/AItems

Untested since I don't have your file structures / data

Roy Lambert

Hi Roy,
As simple as that..  Clearly I have a long way to go with SQL as well.
All good thank you.
Regards,
Ian

So, extending the SQL and having a play with a script I have created the following in EDBMgr to put into a EDBScript..
{sql}
SCRIPT
BEGIN

  EXECUTE IMMEDIATE 'insert into ajobtickets
  select * from jobtickets
  where jobstatus = ''CA'' or (jobstatus = ''CO'' and paid = true and (date_out > date ''2000-01-01'' and date_out < :EndDate))';

  execute immediate 'delete * from JobTickets
  where jobstatus = ''CA'' or (jobstatus = ''CO'' and paid = true and (date_out > date ''2000-01-01'' and date_out < :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}

It Prepares OK but when run I get  the following error..
"ElevateDB Error #700 An error was found in the statement at line 4 and column 22 (Expected expression but instead found
The only ':' is for the Parameter :EndDate and it is probably falling over because the parameter isn't defined.
I have the Parameters tab open but no way to set the parameter value.  Is it possible to set parameters in a script like this?

Regards,
Ian

I think I have started the head banging.
So.  Found out how to get the Parameters set.  Getting closer but still having issues.
Given the following..
{sql}
SCRIPT (IN EndDate Date)
BEGIN
  EXECUTE IMMEDIATE 'insert into aUsersLog
  select * from userslog
  where CAST(DTG as DATE date format ''yyyy-mm-dd'') < ?';

  execute immediate 'delete from userslog
  where CAST(DTG as DATE date format ''yyyy-mm-dd'') < ?';
END
{sql}
n.b.  DTG is a TimeStamp field.
And that EndDate has been set as '2018-02-01' as a Date type, In mode, and that there are records in UsersLog that are prior to 2018-02-01, and the Prepare & Execute appear to be successful, nothing appears in AUsersLog and the relevant records are not deleted from UsersLog.??

Help!!

Regards & TIA,
Ian

Ian


First point is just use CAST(DTG AS DATE) no need for the format stuff

Second point (I think I'm right) is that whilst you've put the placeholder for the parameter you haven't told it which parameter to use. Try

SCRIPT (IN EndDate DATE)
BEGIN
  EXECUTE IMMEDIATE 'insert into aUsersLog
  select * from userslog
  where CAST(DTG as DATE) < ?' USING EndDate;

  execute immediate 'delete from userslog
  where CAST(DTG as DATE) < ?' USING EndDate;
END


Roy Lambert

Hi Roy,
Ah Ha!  Thank you.
A fog lifts.
All good now.

Regards & Tks again,
Ian