Re: ORA-00933: SQL command not properly ended

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:pan.2003.03.12.07.11.15.354164_at_yahoo.com.au...
>
> Why?? Are you trying to make Oracle make up the deficiency by inventing
> data???? ;-)
>
> But whatever works for you... it certainly has never worked comfortably
> for me!
>

Howard,

Well, yes. 'Inventing data' is, I think, actually quite a neat way of putting it. Perhaps I could qualify by saying 'Inventing null data'.

And I certainly to *not* want to get contentious* here.

And I entirely agree that it's not comfortable. I don't like outer joins either; if the data is well organised then it seems to me that they should seldom be necessary.

However, an example:

I am reporting on Peoplesoft data. Peoplesoft is a third party application, which does not employ declarative referential integrity (i.e. in our shop it runs on Oracle, but there are no foreign key constraints).

The requirement (let's say) is to produce a report listing new joiners over the last month, with various details (salary, grade, department and so forth).

Now, Peoplesoft will allow creation of a new employee record (a hire row in PS_JOB) where the 'parent' GRADE does not exist.

So if I use an inner join, and the EMPLID is a logical 'orphan', the row for the unfortunate new recruit is simply 'missed' from the report.

On the other hand, if I use an outer join, putting the plus sign on PS_SALGRADE_TBL (or whatever the damned thing is called), I can at least ensure that the EMPLID is still listed in the report, albeit with the Grade Description blank, or (my preference, I shamelessly admit) showing something like 'Grade missing fom PS_SALGRADE_TBL'.

Regards,
Paul Received on Thu Mar 13 2003 - 14:03:16 CST