Here is an example of a query the runs fine in MS SQL but must be changed for Oracle. I have changed names to protect my company propriety info, but the idea is the same
SELECT X.ObjId, Y.Code AS (some variable called) IT
FROM X, Y, Z (these are tables
WHERE ((X.CurrentData=1) AND (X.Id = Y.CodeId) AND
(X.ObjId = Z.ObjId))
Now when you change this to Oracle you drop the table names and use only the column references so we get:
SELECT objid, code AS (some variable called) IT
FROM X, Y Z (these are tables)
WHERE ((CurrentData=1) AND (.Id = Y.CodeId) AND
(ObjId = ObjId))
And the last AND clause is where my problem comes up. Since the table reference is dropped instead of comparing the column for Z to the column on the X it compares Z with itself.
And the results are erroneous.
I am a novice with Oracle and I know I'm phrasing something wrong in the 2nd query. A restructure of this query would be appreciated.