Hi Guys,
Need some help with a SQL script.
I have a ChangesLog table that records changes to various fields in various tables in my Database.
The following fields are of relevance to this question.
1.   DateTime - A TimeStamp field which is the DateTime of the entry. - TimeStamp.
2.   TableName - The name of th table monitored. - Varchar(20). - e.g. 'JobTickets'.
3.   KeyValue - A key value for tracking.  In the case of JobTickets it is the JobNo. - VarChar(50). - May contain
anything from a part number to to a JobNo or combination.  Only interested in the records that just have a JobNo.
4.   JobNo - In the JobTickets table - Integer.

I have the following script and it works as it is..
{sql}
SCRIPT (IN EndDate Date)
BEGIN
  EXECUTE IMMEDIATE 'insert into aChangesLog
  select * from ChangesLog
  where CAST(DateTime as DATE) < ?' USING EndDate;

  execute immediate 'delete from ChangesLog
  where CAST(DateTime as DATE) < ?' USING EndDate;
END
{sql}

   As part of the overall archiving process JobTickets are moved from the JobTicket table to the AJobTickets table and
then deleted.  All works fine.

   Unfortunately it turns out that the above script scope is too large and archives records that shouldn't be att.

   The Where clause needs to be extended to NOT include those records that still have a JobNo in the JobTickets table.

   So something like the existing where clause, + "and NOT ((TableName = 'JobTickets') and (STRToInt(KeyValue) is in
Jobtickets.JoNo))".  Oh, that looks terrible.  At 67 I shouldn't be trying to learn SQL

   Any assistance/guidance appreciated.

Regards & TIA,
Ian

Ian

>    So something like the existing where clause, + "and NOT ((TableName = 'JobTickets') and (STRToInt(KeyValue) is in
>Jobtickets.JoNo))". Oh, that looks terrible. At 67 I shouldn't be trying to learn SQL

You're almost right - what you need is a subselect

AND (TableName <> 'JobTickets') OR KeyValue NOT IN (SELECT CAST(JobNo AS VARCHAR(50)) FROM JobTickets)

I think its safer casting the integer JobNo to a string than the string KeyValue to and integer when it may not be one. It shouldn't make a difference since the initial tablename test should return false and stop checking but I like belt and braces.

Question: why are you still working at 67 - that's the same as me and I'm living on my pitiful pensions (for a giggle - one pays £23 / month)  and savings? I'm just messing around here to try and stop my brain ossifying!

Roy

Roy Lambert wrote:

> Question: why are you still working at 67 - that's the same as me and I'm living on my pitiful pensions (for a giggle
> - one pays £23 / month)  and savings? I'm just messing around here to try and stop my brain ossifying!

Hi Roy,
   Thanks for that.
   To answer your question - Ditto, pension and all.  Retired for 3 years now, programming and my Customers just keeps my
mind active, more or less, although me physically, much less.
   Plus I get to 'meet' characters like yourself. BG.

Regards,
Ian

Roy Lambert wrote:

> Question: why are you still working at 67 (snip)

At the risk of starting a "mine's bigger than yours" competition, will
you two youngsters please stop implying that 67 is 'old'.

Malcolm (1941)

Roy,
   I ended up with this...

  where (CAST(DateTime as DATE) < ?) AND (TableName <> "JobTickets") AND (KeyValue NOT IN (SELECT CAST(JobNo AS
VARCHAR(50)) FROM JobTickets))' USING EndDate;

   I changed it slightly from yours..  Date is less than :EndDate AND table <> Jobtickets AND Keyvalue isn't in
JobTickets.JobNo.

   What do you rekon?


   Prepares OK.  The damage is already done to the current data import/migration so I will try it Sunday when I do my
next import/migrate from their live ADS data.

Regards,
Ian

Malcolm wrote:
>
> Malcolm (1941)

Only 10 years.  What's the problem you old f#$t?

Ian Branch wrote:

> Malcolm wrote:
> >
> > Malcolm (1941)
>
> Only 10 years.  What's the problem you old f#$t?

<bg>

Hmmm.  Looking at the ChangesLog data att, Keyvalue could be, by example, 1008 or 1008:AP

Hmmm.  Looking at the ChangesLog data att, Keyvalue could be, by example, 1008 or 1008:AP.
Both could be valid records to be transferred if 1008 is no longer a valid Jobtickets JobNo.

Hmmm.  Looking at the ChangesLog data att, Keyvalue could be, by example, 1008 or 1008:AP.
Both could be valid records to be transferred if 1008 is no longer a valid Jobtickets JobNo.
I think the test needs to be if the AJobTickets JobNo is in KeyValue.