Gregg, what I have figured on this is the following:
The revised query below gets 728 records but instead of giving me difference in days I simply output the columns themselves. I will have to research what the prevous person did in MS SQL Server more closely:
SELECT id, objid, createdate,
trunc(end_date -createdate) - trunc(start_date -createdate) as difference
FROM HNEMap, greggs_date
where active =1;
But maybe you can be of help with an ORA 942 error I'm getting. You see I've been tasked with converting many queries in MS SQL Server to their Oracle equivalent.
The query below fails with ORA 942, But I can't see why every single table exists and I don't believe it's a matter of privileges.
SELECT IdMap.ObjId, IdMap.Id AS Mrn, IdMap.AsgnAuthorityId,IdMap.SystemId,HNEAudit.MsgControlId,HNEAudit.CreateDate,HNEAudit.HNEAuditID
FROM IdMap, HNEMap, greggs_dict_triggers, HNeAudit
WHERE ((IdMap.Type='MRN') AND (IdMap.Active=1) AND (HNEMap.Active=1) AND (IdMap.ObjId = HNEMap.ObjId)
AND ((IdMap.SystemId=50440692) Or (IdMap.SystemId=161983401)) AND (IdMap.Id=greggs_dict_triggers.MRN) AND (IdMap.AsgnAuthorityId=greggs_dict_triggers.AsgnAuthorityId) AND (HNEAudit.ObjId=IdMap.ObjId) )
ORDER BY HNEAudit.HNEAuditID
To "see" a table you need the following:
a) you need to own the table, or
b) you have a private synonym pointing to the table in another schema, or
c) a public synonym points to the table in another schema.
In any case, you also need privileges on the table in question.
without more information, no I can't help any more.
Try showing results of these queries:
select owner, object_name, object_type from dba_objects where object_name in ( 'IDMAP', 'HNEMAP', 'GREGGS_DICT_TRIGGERS', 'HNEAUDIT', 'IdMap', 'HNEMap', 'HNeAudit' );