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...

And (missed it), Antares has even better reasons...

Heh... there's just a pot-wad of undocumented features and sprocs in SQL Server... most would warn against using them because if they're undocumented, they could disappear on the next service...

For me, it's because most things that you query are tables... for a view or a function, you might want to use the Hungarian notation to make it obvious that...