Re: [DUG]: Another SQL poser
Seth Have figured out that I can do this in two bits anyway, by updating an already existing and related table first, to capture the current forcode/comp combinations from "dockets" and then using this table to control the delete from "dockets". I might try running it overnight just for interest. Appreciate your help. Mark - Original Message - From: "Seth Wagoner" <[EMAIL PROTECTED]> To: "Multiple recipients of list delphi" <[EMAIL PROTECTED]> Sent: Saturday, May 26, 2001 7:40 PM Subject: Re: [DUG]: Another SQL poser > On 26 May 2001, at 14:14, Mark Howard wrote: > > > Seth said > > > > > > I will only let you use this answer if you put your system clock > > > back to today. :-) Furthermore, I don't guarantee it's correct, but > > > I think it accomplishes what you want to achieve: > > My system clock IS correct: my files are being stamped with the correct time > > and date. > > The problem is with my email only. If I send one to myself: the time and > > date in Sent Items is correct but the time and date in the Inbox shows 19 > > hours earlier. Also everyone elses' mail coming in is similarly stamped as > > coming in 19 hours earlier. Very strange. If someone has a fix - I'll stop > > stuffing up the order in your Inboxes! > > Weirdness. What email program are you using? > > > > delete from dockets d1 where not exists > > > (select forcode from dockets d2 where d1.forcode = d2.forcode and > > > d1.comp = d2.comp and d2.docketdate > '31/03/2001') > > > > > Well, Seth, you must be a pretty good teacher - this is what I had. But > > after an hour it was still running so I canned it. Maybe it just needed > > more time. It there any way of monitoring progress? - or do you just have > > to have faith? > > I don't know a lot about Paradox, unfortunately. But perhaps it's > implemented such that the nested select is completely evaluated > rather than just taking a single row to decide the exists/not-exists. > That could slow it down quite a bit. I'd say if it's something you > only have to do once a year, let it run overnight and see what > happens. You could also try putting in a distinct and see if that > helped: > > delete from dockets d1 where not exists (select distinct forcode from > dockets d2 where d1.forcode = d2.forcode and > d1.comp = d2.comp and d2.docketdate > '31/03/2001') > > Cheers, > > Seth. > === > Seth Wagoner, WebFoundry Ltd. Christchurch, New Zealand > mailto:[EMAIL PROTECTED]http://webfoundry.co.nz > > -- - > New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED] > Website: http://www.delphi.org.nz > To UnSub, send email to: [EMAIL PROTECTED] > with body of "unsubscribe delphi" --- New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED] Website: http://www.delphi.org.nz To UnSub, send email to: [EMAIL PROTECTED] with body of "unsubscribe delphi"
Re: [DUG]: Another SQL poser
Nello How on earth did my time zone get set to Tijuana?! I hope you like chocolate fish! Thanks Mark - Original Message - From: "Nello Sestini" <[EMAIL PROTECTED]> To: "Multiple recipients of list delphi" <[EMAIL PROTECTED]> Sent: Saturday, May 26, 2001 7:49 PM Subject: Re: [DUG]: Another SQL poser > Mark > > > > My system clock IS correct: my files are being stamped with the correct > time > > > and date. > > your clock may be correct - but is your time zone correct? > > stamps i get from you have reasonable looking times and dates - but the > zone is -0700.should be +1200 (the error being 19hrs) > > -ns > > > > -- - > New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED] > Website: http://www.delphi.org.nz > To UnSub, send email to: [EMAIL PROTECTED] > with body of "unsubscribe delphi" > --- New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED] Website: http://www.delphi.org.nz To UnSub, send email to: [EMAIL PROTECTED] with body of "unsubscribe delphi"
Re: [DUG]: Another SQL poser
Mark > > My system clock IS correct: my files are being stamped with the correct time > > and date. your clock may be correct - but is your time zone correct? stamps i get from you have reasonable looking times and dates - but the zone is -0700.should be +1200 (the error being 19hrs) -ns --- New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED] Website: http://www.delphi.org.nz To UnSub, send email to: [EMAIL PROTECTED] with body of "unsubscribe delphi"
Re: [DUG]: Another SQL poser
On 26 May 2001, at 14:14, Mark Howard wrote: > Seth said > > > > I will only let you use this answer if you put your system clock > > back to today. :-) Furthermore, I don't guarantee it's correct, but > > I think it accomplishes what you want to achieve: > My system clock IS correct: my files are being stamped with the correct time > and date. > The problem is with my email only. If I send one to myself: the time and > date in Sent Items is correct but the time and date in the Inbox shows 19 > hours earlier. Also everyone elses' mail coming in is similarly stamped as > coming in 19 hours earlier. Very strange. If someone has a fix - I'll stop > stuffing up the order in your Inboxes! Weirdness. What email program are you using? > > delete from dockets d1 where not exists > > (select forcode from dockets d2 where d1.forcode = d2.forcode and > > d1.comp = d2.comp and d2.docketdate > '31/03/2001') > > > Well, Seth, you must be a pretty good teacher - this is what I had. But > after an hour it was still running so I canned it. Maybe it just needed > more time. It there any way of monitoring progress? - or do you just have > to have faith? I don't know a lot about Paradox, unfortunately. But perhaps it's implemented such that the nested select is completely evaluated rather than just taking a single row to decide the exists/not-exists. That could slow it down quite a bit. I'd say if it's something you only have to do once a year, let it run overnight and see what happens. You could also try putting in a distinct and see if that helped: delete from dockets d1 where not exists (select distinct forcode from dockets d2 where d1.forcode = d2.forcode and d1.comp = d2.comp and d2.docketdate > '31/03/2001') Cheers, Seth. === Seth Wagoner, WebFoundry Ltd. Christchurch, New Zealand mailto:[EMAIL PROTECTED]http://webfoundry.co.nz --- New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED] Website: http://www.delphi.org.nz To UnSub, send email to: [EMAIL PROTECTED] with body of "unsubscribe delphi"
Re: [DUG]: Another SQL poser
Neven I was relying on my memory of Informix SQL (from 14 years ago!). Just had a check, and the "into temp" goes at the end of the select clause. Very handy. But this does not work in Paradox, neither does your "Insert into temp" unless you go through the create table bizzo first. Never mind. I have a solution - there is a related table that I can update first, so that it just has the forcode/comp combinations that we want. Obvious really - but not on a Friday afternoon! Thanks for your input Mark - Original Message - From: Neven MacEwan To: Multiple recipients of list delphi Sent: Friday, May 25, 2001 1:02 AM Subject: Re: [DUG]: Another SQL poser Mark is "select into TableName" std SQL, I would have expected "Insert into temp select " If not I could say its Paradox not SQL Neven - Original Message - From: Mark Howard To: Multiple recipients of list delphi Sent: Saturday, May 26, 2001 11:46 AM Subject: [DUG]: Another SQL poser Hello I'm using Paradox. I want to achieve the following effect: Select into temp distinct a.forcode, a.comp from dockets a where a.docketdate > '31/03/2001'; Delete from dockets b where not exists (select c.forcode from temp where b.forcode = temp.forcode and b.comp = temp.comp) BUT with Paradox I can't use the "into temp" syntax Can someone suggest another way of acheiving the same. In a nutshell, I want to delete all the rows from the dockets table where the forcode/comp combination is not current (ie been used this financial year) Any ideas? TIA Mark
Re: [DUG]: Another SQL poser
Seth said > > I will only let you use this answer if you put your system clock > back to today. :-) Furthermore, I don't guarantee it's correct, but > I think it accomplishes what you want to achieve: My system clock IS correct: my files are being stamped with the correct time and date. The problem is with my email only. If I send one to myself: the time and date in Sent Items is correct but the time and date in the Inbox shows 19 hours earlier. Also everyone elses' mail coming in is similarly stamped as coming in 19 hours earlier. Very strange. If someone has a fix - I'll stop stuffing up the order in your Inboxes! > > delete from dockets d1 where not exists > (select forcode from dockets d2 where d1.forcode = d2.forcode and > d1.comp = d2.comp and d2.docketdate > '31/03/2001') > Well, Seth, you must be a pretty good teacher - this is what I had. But after an hour it was still running so I canned it. Maybe it just needed more time. It there any way of monitoring progress? - or do you just have to have faith? The dockets table has about 17,000 rows. Mark > --- New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED] Website: http://www.delphi.org.nz To UnSub, send email to: [EMAIL PROTECTED] with body of "unsubscribe delphi"
Re: [DUG]: Another SQL poser
Mark is "select into TableName" std SQL, I would have expected "Insert into temp select " If not I could say its Paradox not SQL Neven - Original Message - From: Mark Howard To: Multiple recipients of list delphi Sent: Saturday, May 26, 2001 11:46 AM Subject: [DUG]: Another SQL poser Hello I'm using Paradox. I want to achieve the following effect: Select into temp distinct a.forcode, a.comp from dockets a where a.docketdate > '31/03/2001'; Delete from dockets b where not exists (select c.forcode from temp where b.forcode = temp.forcode and b.comp = temp.comp) BUT with Paradox I can't use the "into temp" syntax Can someone suggest another way of acheiving the same. In a nutshell, I want to delete all the rows from the dockets table where the forcode/comp combination is not current (ie been used this financial year) Any ideas? TIA Mark
Re: [DUG]: Another SQL poser
On 25 May 2001, at 16:46, Mark Howard wrote: > Hello > I'm using Paradox. > > I want to achieve the following effect: > > Select into temp > distinct a.forcode, a.comp from dockets a > where a.docketdate > '31/03/2001'; > > Delete from dockets b > where not exists > (select c.forcode from temp > where b.forcode = temp.forcode > and b.comp = temp.comp) > > BUT with Paradox I can't use the "into temp" syntax > > Can someone suggest another way of acheiving the same. > > In a nutshell, I want to delete all the rows from the dockets table where the >forcode/comp combination is not current (ie been used this financial year) I will only let you use this answer if you put your system clock back to today. :-) Furthermore, I don't guarantee it's correct, but I think it accomplishes what you want to achieve: delete from dockets d1 where not exists (select forcode from dockets d2 where d1.forcode = d2.forcode and d1.comp = d2.comp and d2.docketdate > '31/03/2001') Seth. === Seth Wagoner, WebFoundry Ltd. Christchurch, New Zealand mailto:[EMAIL PROTECTED]http://webfoundry.co.nz --- New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED] Website: http://www.delphi.org.nz To UnSub, send email to: [EMAIL PROTECTED] with body of "unsubscribe delphi"
Re: [DUG]: Another SQL poser
Further to my post below: I've found that in the DBD if I Execute the first query (without "into temp") Rename ANSWER.DB to TEMP.DB Execute the second query then I get what I want. Is there a way in Delphi to create a virtual Temp.db file in memory that can be referenced by the second query? Or (better) is there a more elegant solution? Mark - Original Message - From: Mark Howard To: Multiple recipients of list delphi Sent: Friday, May 25, 2001 4:46 PM Subject: [DUG]: Another SQL poser Hello I'm using Paradox. I want to achieve the following effect: Select into temp distinct a.forcode, a.comp from dockets a where a.docketdate > '31/03/2001'; Delete from dockets b where not exists (select c.forcode from temp where b.forcode = temp.forcode and b.comp = temp.comp) BUT with Paradox I can't use the "into temp" syntax Can someone suggest another way of acheiving the same. In a nutshell, I want to delete all the rows from the dockets table where the forcode/comp combination is not current (ie been used this financial year) Any ideas? TIA Mark
