Sorry about that guys.  Deletes in the NG don't carry into the Forum.  Duly noted.

Malcolm


That beats Methuselah only managed 969. so to reach the age of one thousand nine hundred and forty one years old is some achievement.

BTW how wrinkly do you get at that age?

Roy Lambert

Ian


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

Won't work. Not only do you have to learn SQL you also have to learn boolean logic

Lets assume (CAST(DateTime as DATE) < ?) results in TRUE

So if TableName is FredBloggs you get

TRUE AND TRUE AND (either TRUE or FALSE - its unlikely that a table other than JobTickets will have a valid JobNo, but it is possible so most likely result is TRUE)

evaluates to TRUE so backed up

If TableName is JobTickets you get

TRUE AND FALSE AND (either TRUE or FALSE but who cares since the test fails anyway)

evaluates to FALSE so no backup

So either my logic is wrong or, as you have it written, you'll never get anything from JobTickets. By CASTing JobNo rather than KeyValue that should protect against invalid CASTs

Try this


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

Roy

ps I often end up scribbling truth tables on a bit of paper cos its hard to get your head round some of the convolutions

Ian

>Sorry about that guys. Deletes in the NG don't carry into the Forum. Duly noted.

I thought you had hiccoughs there.

I much prefer Tim's system where you can't edit ie retroactively alter things to put yourself in the right

Roy

Roy Lambert wrote:

> Malcolm
>
>
> That beats Methuselah only managed 969. so to reach the age of one
> thousand nine hundred and forty one years old is some achievement.
>
> BTW how wrinkly do you get at that age?
>
> Roy Lambert

Hi Roy.  

My philosopher's stone is looking like a prune now that I have
extracted so much of its Elixir of Life.  My strategy is to take a
fresh swig when my wrinkles look as bad as the stone's.

The advantage of experiencing so much history is not all it's cracked
up to be.  Now if I could just look that far ahead, Gates and his like
would not get a look in.  

Roy Lambert wrote:

> Try this
>
>
> (CAST(DateTime as DATE) < ?)
> AND
> ((TableName <> "JobTickets") OR (KeyValue NOT IN (SELECT CAST(JobNo AS VARCHAR(50)) FROM JobTickets)));
>
> Roy
>
> ps I often end up scribbling truth tables on a bit of paper cos its hard to get your head round some of the
> convolutions

Hi Roy,
   I see what your are doing however if the keyvalue is say 1008 and JobNo 1008 has alreagy been moved/deleted from
JobTickets, then it will rightly not appear in "SELECT CAST(JobNo AS VARCHAR(50)) FROM JobTickets" and be moved.  If
the ChangesLog keyvalue is 1010;AR and JobNo 1010 is still in JobTickets it will appear in "SELECT CAST(JobNo AS
VARCHAR(50)) FROM JobTickets" but 1010:AR will get moved anyway.

   Haven't done a truth table in decades.

   Appreciate your time on this.

Regards,

Ian

Hi Roy,
See attached.  Job Numbers 628 & 629 are still present in the JobTickets table.

Regards,
Ian

Ian

>    I see what your are doing however if the keyvalue is say 1008 and JobNo 1008 has alreagy been moved/deleted from
>JobTickets, then it will rightly not appear in "SELECT CAST(JobNo AS VARCHAR(50)) FROM JobTickets" and be moved. If
>the ChangesLog keyvalue is 1010;AR and JobNo 1010 is still in JobTickets it will appear in "SELECT CAST(JobNo AS
>VARCHAR(50)) FROM JobTickets" but 1010:AR will get moved anyway.

From an earlier post where you were casting KeyValue to integer I thought it was when it referred to a JobTicket.

You are quite right '1010;AR' will NEVER equal '1010' <vbg> So it will get a little more complex.

(CAST(DateTime as DATE) < ?)
AND
(
 (TableName <> "JobTickets")
 OR
(

CAST(IF(POS(';','KeyValue)>0,
SUBSTR('1010;AR',1,POS(';',KeyValue)-1),
'-1') AS INTEGER)

NOT IN (SELECT JobNo AS VARCHAR(50)) FROM JobTickets));


Not very well formatted, and I may have the odd quote where it shouldn't be but it should give you the right idea. If KeyValue doesn't have a ; and does have non-numeric characters the result of the cast will be a NULL. I have no idea what you might want to do with that.

Roy

Ian


>See attached. Job Numbers 628 & 629 are still present in the JobTickets table.

I assume you're right but your screenshot is against ChangesLog.

What I find helpful there is to have the various tests as columns and make sure my logic is right

Is it possible for you to put together a small test system with some data, email it to me so that I can look and see?

Roy

Hi Roy,
   Example in email, hopefully.

Regards,
Ian