Thank you Fred, PL/SQL and LPAD did the trick nicely. Eric _____
From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Grooms, Frederick W Sent: Wednesday, June 18, 2008 11:25 AM To: [email protected] 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: [email protected] 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 __Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" html___ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
