Here's an MS-SQL equivalent for anyone who's interested...
declare @intLoopVar as int;
declare @caseid as varchar(15);
set @intLoopVar = 1;
while @intLoopVar <= 1000000
begin
set @caseid = (select 'HD' + REPLICATE('0',(13 - LEN(CAST(@intLoopVar AS
VARCHAR(13))))) + CAST(@intLoopVar AS VARCHAR(13)));
if not exists (select * from BGI_SDHP_Modify where case_id = @caseid)
print @caseid;
set @intLoopVar = @intLoopVar + 1;
end
----- Original Message -----
From: "Thomas Bean" <[EMAIL PROTECTED]>
Newsgroups: gmane.comp.crm.arsystem.general
To: <arslist@ARSLIST.ORG>
Sent: Wednesday, June 18, 2008 12:03 PM
Subject: Re: SQL to find missing records
That's a cool trick, I'll have to see if I can replicate that in MS-SQL
(T-SQL).
----- Original Message -----
From: "Grooms, Frederick W" <[EMAIL PROTECTED]>
Newsgroups: gmane.comp.crm.arsystem.general
To: <arslist@ARSLIST.ORG>
Sent: Wednesday, June 18, 2008 10:25 AM
Subject: Re: SQL to find missing records
Try something like the following (This is off the top of my head)
declare
intLoopVar integer;
begin
for intLoopVar from 1..1000000
loop
Select intLoopVar from dual where (Select CASE_ID from
BGI_SDHP_Modify where CASE_ID = 'HD' || LPAD(intLoopVar,13,'0') ) IS
NULL;
END LOOP;
end;
Fred
________________________________
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Eric Cleereman (IT)
Sent: Wednesday, June 18, 2008 10:06 AM
To: arslist@ARSLIST.ORG
Subject: SQL to find missing records
Hi All,
I have a form containing sequential Case IDs as follows:
HD0000000000001
...
HD0000001000000
When I run an SQL count on this, it only returns a count of 999,997
results:
SELECT COUNT(CASE_ID) FROM BGI_SDHP_Modify
999997
I have an export with all 1,000,000 records, prior to 3 of them going
missing. I'd like to find which 3 records are missing, so I can
reimport just those 3.
Does anyone have an SQL statement which would return the Case IDs of the
3 missing records?
ARServer is 6.3, Patch 21 on AIX 5.2. The database is Oracle 9i as a
local database.
Eric Cleereman
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
