Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 5 of 5 total |
Can someone explain this please |
Mon, Jul 7 2014 11:32 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | I screwed up today and deleted some stuff I shouldn't. I don't want to restore from last night because then I'd have to go through deleting the stuff that should have been deleted again.
I copied a backup back to where I could use it. My confusion starts when I run the following query select * from contacts where _Status in ('CV on file', 'Placed', 'Hold') Using the live data I get 643 rows and using the old data its 647 However, when I run select * from contacts where _Status in ('CV on file', 'Placed', 'Hold') AND _ID not in (SELECT _ID from live.Contacts) from the old database I get 7 I then tried select COUNT(*) from old.contacts union select COUNT(*) from live.contacts union select count(*) from contacts where _ID not in (SELECT _ID from live.Contacts) and got 12961 12003 962 I'm sure there is a perfectly simple explanation and as soon as someone gives it to me I'll go ahh - its obvious, but I've screwed up enough today I don't want to continue until I know what I'm doing. Roy Lambert |
Mon, Jul 7 2014 5:22 PM | Permanent Link |
Michael Riley ZilchWorks | Roy Lambert wrote:
> select * from contacts where _Status in ('CV on file', 'Placed', > 'Hold') What happens when you force an UPPERCASE check: select * from contacts where UPPER(_Status) in ('CV ON FILE', 'PLACED', 'HOLD') -- Michael Riley GySgt USMC (Ret) www.zilchworks.com |
Tue, Jul 8 2014 2:59 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Michael
>> select * from contacts where _Status in ('CV on file', 'Placed', >> 'Hold') > >What happens when you force an UPPERCASE check: > >select * from contacts where UPPER(_Status) in ('CV ON FILE', 'PLACED', >'HOLD') I haven't tried but it will/should make no difference since both tables are identical in structure (one's just a few days older), and have the column defined as ANSI_CI Roy |
Wed, Jul 9 2014 1:08 PM | Permanent Link |
Adam Brett Orixa Systems | Roy
I will try to help ... not sure how much! >>Using the live data I get 643 rows and using the old data its 647 >>select COUNT(*) from old.contacts >>union >>select COUNT(*) from live.contacts >>union >>select count(*) from contacts >>where >>_ID not in (SELECT _ID from live.Contacts) >>and got >>12961 >>12003 >>962 12003 + 962 - 12961 = 4 and 647 - 643 = 4 ... so you seem to have 4 items in Old.Contacts which are not in NEW. If you have an integer based ID field on the tables you can try: SELECT ID FROM Old.Contacts WHERE NOT (ID IN (SELECT ID FROM New.Contacts) OR ID IN (SELECT ID FROM Live.Contacts)) That should pinpoint the specific Old.Contacts which are not in either of the other tables. If there are 4 of these you have tied up part of the problem. Good luck. |
Thu, Jul 10 2014 4:28 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Adam
>.. so you seem to have 4 items in Old.Contacts which are not in NEW. I'm not sure how you reach that conclusion. I would prefer to say that I have 4 rows where this filter (_ID not in (SELECT _ID from live.Contacts)) in the WHERE clause does not identify the gap. >If you have an integer based ID field on the tables you can try: > >SELECT ID FROM Old.Contacts WHERE NOT (ID IN >(SELECT ID FROM New.Contacts) OR ID IN (SELECT ID FROM Live.Contacts)) > >That should pinpoint the specific Old.Contacts which are not in either of the other tables. If there are 4 of these you have tied up part of the problem. Crossed wire somewhere - there are only two tables - the old version and the new (up to date) version aka live version. Sorry for the confusion. I just went ahead and restored the ones it said were missing (after backing up the live data). I had no collisions on the _ID column which is the primary key so things should be OK. Roy Lambert |
This web page was last updated on Tuesday, May 14, 2024 at 07:14 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |