but when you're dealing in hundreds of millions of records with finite resources you have to delete it sometime.....and when the garbage man comes around to remove the records flagged...

Hmmm... and what do you do for sprocs that are used by multiple applications?

"Steal" code from those...

And, I'm not so sure about the need for the cross join... but I'm on my way out for the day...

Oracle is very picky about the reuse of table aliases within a given query... try this...

SELECTP.patno, P.name, P.docno, P.docname, P.specialization, COALESCE( T.NoOfTreatments, 0 ) AS NoOfTreatments

FROM(

SELECTPa.patno, Pa.name, D.docno, D.docname,...

Heh... "A Developer must not guess... a Developer must KNOW"...

... especially in a political environment where you are challenging incumbent "knowledge".

One of the most difficult things you can do is...

Try this instead...

--DROP TABLE dbo.FirstName,dbo.LastName

--===== Create a couple of test tables

CREATE TABLE dbo.FirstName

(

...

Thanks, Cali. I appreciate the feedback.

Just a suggestion...

If you regularly load large amounts of data using BCP or Bulk Insert, consider making a "staging area" database. Because all data in a staging area should...

Ummmm... OK... Guess I don't understand... why are you keeping any rows for a long period of time in a "staging area"... all of that data should be expendable to...

Or... you could kick off the proc and do a WAITFOR DELAY 'hh:mm:ss'.

Of course, if the server get's taken down or the job ever fails, you need to manually restart...

Also - your test has a flaw in it, since you start by trying to cram 4 digits after the decimal point into 2 digits after the dot. So you...

Heh... You do DELETEs? In a "staging area"? I'd just mark the rows with a flag of some sort and tell my procs to ignore those rows.

Heh... at that point... I'd be thinking "BEER"! 😀

Even if it's "auto-generated" code, someone someday is going to have to troubleshoot it... take some time out to format it! And, the use of table aliases would clear...